MySQL Script for find finding missing entry from a specific group and then enter the missing row on that group

I have one table ClockIn which is as follows:

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.

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

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

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0