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.
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.
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' );
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