May 19, 2023 at 5:52 pm
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!
May 20, 2023 at 6:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
May 22, 2023 at 10:10 am
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 #t
VALUES (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_minutes
FROM #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);
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply