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.
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".