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