-1

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.

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

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

enter image description here

0

Test

SELECT *
FROM sampledata
WHERE `Time` + CASE WHEN Shift_Start_Time > Shift_End_Time
                                           THEN '24:00'
                                           ELSE '00:00'
                                           END 
      BETWEEN Shift_Start_Time 
          AND Shift_End_Time + CASE WHEN Shift_Start_Time > Shift_End_Time
                                    THEN '24:00'
                                    ELSE '00:00'
                                    END
| improve this answer | |
0

Have you considered storing as datetime rather than using separate date and time fields? This would make the issue of the night shift occurring over two days a lot more manageable

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.