# Determining Time Worked Between Time Pairs During Certain Hours of Higher Pay

• We have nurses pulling night shifts. They get bonus pay (more \$\$\$ per hour) during the hours of 10pm and 5am.

They check in and we capture their check-in time and check out time.  How can I calculate the amount of time the worked during the overtime pay hours?  I know SQL pretty OK - but I am a little unsure how to approach this

Let me give a scenario:

Check-In: 7:37PM   Check-Out 10:45pm

Check-In: 11:37PM   Check-Out 3:45AM

Check-In: 4:37AM   Check-Out 7:00am

In this case - the nurse checked in BEFORE the overtime pay started, check-out during the overtime pay, made a check-in/out that was fully encompassed by the overtime pay, and then did another that started in overtime pay and ended in regular pay.

I  need know how many minutes were worked 1) In regular page and 2) In overtime pay....

One scenario not covered in the above example is if someone checks in on both sides of the overtime pay like:

Check-In: 9:00pm  Check-Out 7:00am...

Your help would be greatly appreciated. If you can point me in a good direction I can do all the rest - just looking for someone who knows what direction that is. Thank you!

• Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.

• You will get a quicker reply if you post consumable test data:

`CREATE TABLE #t(   emp_id int NOT NULL   ,check_in datetime NOT NULL   ,check_out datetime NOT NULL   ,PRIMARY KEY NONCLUSTERED (emp_id, check_in));INSERT INTO #tVALUES (1, '20230518 19:37', '20230518 22:45')   ,(2, '20230518 23:37', '20230519 03:45')   ,(3, '20230519 04:37', '20230519 07:00')   ,(4, '20230519 21:00', '20230520 07:00');`

You will get a better replay if your test data includes all possible combinations. You will probably have to refine the following:

`SELECT S.emp_id, S.check_in, S.check_out   ,M.total_minutes - M.overtime_minutes AS regular_minutes   ,M.overtime_minutesFROM #t S   CROSS APPLY (VALUES (DATEDIFF(day, 0, S.check_in))) D (OffSet)   CROSS APPLY   (      VALUES      (         DATEADD(day, -D.OffSet, S.check_in)         ,DATEADD(day, -D.OffSet, S.check_out)      )   ) X (check_in, check_out)   CROSS APPLY   (      -- This works for given data.      VALUES      (         CASE            WHEN X.check_in >= '19000101 22:00'               AND X.check_out < '19000102 05:00'            THEN DATEDIFF(minute, X.check_in, X.check_out)            WHEN X.check_in >= '19000101 00:00'               AND X.check_out < '19000101 05:00'            THEN DATEDIFF(minute, X.check_in, X.check_out)            WHEN X.check_in < '19000101 05:00'               AND X.check_out >= '19000101 05:00'            THEN DATEDIFF(minute, X.check_in, '19000101 05:00')            WHEN X.check_in < '19000101 22:00'               AND X.check_in >= '19000101 05:00'               AND X.check_out < '19000102 05:00'            THEN DATEDIFF(minute, '19000101 22:00', X.check_out)            WHEN X.check_in >= '19000101 22:00'               AND X.check_out >= '19000102 05:00'            THEN DATEDIFF(minute, X.check_in, '19000102 05:00')            WHEN X.check_in < '19000101 05:00'               AND X.check_out >= '19000102 05:00'            THEN DATEDIFF(minute, X.check_in, '19000101 05:00')            WHEN X.check_in < '19000101 22:00'               AND X.check_out >= '19000102 05:00'            THEN DATEDIFF(minute, '19000101 22:00', '19000102 05:00')         END         ,DATEDIFF(minute, X.check_in, X.check_out)      )   ) M (overtime_minutes, total_minutes);`

• This reply was modified 4 months, 1 week ago by  Ken McKelvey.
• This reply was modified 4 months, 1 week ago by  Ken McKelvey.

Viewing 3 posts - 1 through 2 (of 2 total)