We have Date, Time, EmpId, InOut, Shift_Start_Time and Shift_End_Time of an employee.
Need your help to write a sql for these conditions: 1.Consider the records which Time is falling between Shift_Start_Time and Shift_End_Time. If the In/Out time is outside the shift_start & Shift_End time, ignore them. Ex:Shift_Start_Time is 18:00:00 and Shift_End_Time is 07:00:00. If my In time is before 18:00 or Out time is after 07:00, no need to consider.
Take first IN and Last OUT between Shift_Start and Shift_End time window. Note:Here Date will change for night shift. Ex: First IN time is 18:30 on 03/04/2020 and OUT time is 06:30 on 03/05/2020
If it is day shift, simply take first IN and last OUT time of an emp and this time should be between Shift_Start and Shift_End time.