Home > Software engineering >  MySQL Script for find finding missing entry from a specific group and then enter the missing row on
MySQL Script for find finding missing entry from a specific group and then enter the missing row on

Time:10-22

I have one table ClockIn which is as follows:

Id TimeStamp WorkDayId EmployeeId Type
1 2021-10-19 08:00:00 148 1 Start
2 2021-10-19 10:00:00 148 1 End
3 2021-10-19 11:00:00 148 1 Start
4 2021-10-19 08:00:00 149 2 Start
5 2021-10-19 17:00:00 149 2 End

The table is basically using for attendance of the employee. I need to write one SQL script which will find the employee who has any start but forgot to enter end time (Means he has no rows for Type=end, but has start. NB: One employee might have multiple Start and End in the same day).

First I need to figure out that employee and then I have to enter the missing End row for that employee. And the End TimeSpan will be StartTime 8hrs.

I am using MySql DB, so I am trying to write a MySql script. Do I need to use any temporary table or procedure for this. I am totally stuck how to do that. Any help would be appreciate please.

CodePudding user response:

Check with NOT EXISTS another record for same employee, same day, "End" timestamp is after the timestamp for "Start"

SELECT a.*
FROM ClockIn a
WHERE a.Type = 'Start'
AND NOT EXISTS (
  SELECT 1
  FROM ClockIn b
  WHERE b.EmployeeId = a.EmployeeId
  AND a.WorkDayId = b.WorkDayId
  AND a.Timestamp < b.Timestamp
  AND b.Type = 'End'
);

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=bf83dfce56500b12cc945848e94e8b9a

Examples in the above link also includes an INSERT statement for create the "End" record, but it is a question if it always add 8 hours from "Start".

  • Related