Home List Hours worked with multiple shifts and across dates

# List Hours worked with multiple shifts and across dates

user3446684
1#
user3446684 Published in 2017-12-06 17:52:47Z
 I have a few questions on how to handle a few employee timekeeping queries in SQL 2012… I had asked another quesiton last week about determining the time between shifts, which I as able to get a great response to. We are fed the data from our Point Of Sale Software Provider, so we cannot change the format of the data. Work days are based on DateOfBusiness which are from 5:00 am until 4:59am the next calendar day, so it crosses midnight. Locations End Of Day process is offset a little to control balance loads, some are 5:05am instead of 5:00am. When the End Of Day process runs, it clocks everyone out then clocks them back in when the process completes. - Employees clock out for the 30 minute required meal breaks, so there are 2 or more records for one day for these employees. Not sure why, but several employees have more than 3 records in one day (EmployeeShiftNumber). - Shifts occasionally cross DateOfBusiness. In at 11:00PM - Out at 7:00am I need to report list of initial InTime and Final OutTime and the number of minutes worked. I have will have to compare these against the table which holds the employee schedules. This reporting is not for Payroll purposes, but for comparisons against scheduled shifts. I have included some sample data grouped by employee and by day and the values I would expect to see commented to the right. I also need to see the number of minutes worked in the shift. --drop table #Shift CREATE TABLE #Shift( FKEmployeeNumber int, DateOfBusiness datetime, FKStoreId int, EmployeeShiftNumber int, FKJobCodeId int, InHour int, InMinute int, OutHour int, OutMinute int) insert into #Shift ( FKEmployeeNumber, DateOfBusiness, FKStoreId, EmployeeShiftNumber, FKJobCodeId, InHour, InMinute,OutHour,OutMinute) values (23761, '11/30/2017', 3013, 0, 1, 17, 39, 21, 30), (23761, '11/30/2017', 3013, 1, 1, 21, 30, 2, 39), -- 5:39PM 2:39AM (23770, '11/30/2017', 3013, 0, 200, 7, 19, 16, 25), -- 7:19AM 4:25PM (23938, '11/30/2017', 3013, 0, 1, 16, 4, 1, 26), -- 4:04AM 1:26AM (24006, '11/30/2017', 3013, 0, 1, 7, 30, 18, 36), (24006, '11/30/2017', 3013, 1, 1, 18, 36, 18, 40), -- 7:30AM 6:40PM (24018, '11/30/2017', 3013, 0, 2, 8, 52, 17, 0), -- 8:52M 4:00PM (25176, '11/30/2017', 3013, 0, 200, 15, 59, 20, 1), -- 3:59PM 8:01PM (25176, '11/30/2017', 3013, 1, 200, 20, 30, 0, 05), -- 8:30PM 12:05AM (25180, '11/30/2017', 3013, 0, 1, 21, 0, 5, 0), -- 9:00PM 5:00AM (25187, '11/30/2017', 3013, 0, 1, 10, 0, 16, 6), -- 10:00AM 4:06PM (35189, '11/30/2017', 3013, 0, 1, 16, 58, 2, 4), -- 4:58PM 2:04AM (25147, '12/04/2017', 3106, 0, 1, 6, 58, 15, 2), (25147, '12/04/2017', 3106, 1, 1, 15, 3, 15, 3), -- 6:58AM 3:03PM (26291, '12/01/2017', 3118, 1, 200, 23, 15, 5, 5), (26291, '12/02/2017', 3118, 0, 200, 5, 6, 7, 22), -- 11:15PM 7:22AM (26291, '12/03/2017', 3118, 0, 200, 7, 30, 15, 38), -- 7:30AM 3:38PM (26291, '12/04/2017', 3118, 0, 200, 23, 15, 5, 5), (26291, '12/05/2017', 3118, 0, 200, 5, 6, 7, 12), -- 11:15PM 7:12AM (26291, '12/05/2017', 3118, 1, 200, 23, 15, 5, 5), (26291, '12/06/2017', 3118, 0, 200, 15, 14, 7, 5) -- 11:15PM 7:05AM --Select * from #Shift SELECT fkstoreid, FKEmployeeNumber AS EmpID, DateOfBusiness AS Date, RIGHT(CONVERT(varchar(30), DATEADD(MINUTE, InTime_Mins, 0), 100), 7) AS InTime, RIGHT(CONVERT(varchar(30), DATEADD(MINUTE, OutTime_Mins, 0), 100), 7) AS OutTime, MinsWorked FROM ( select sh.FKStoreId, sh.FKEmployeeNumber, sh.DateOfBusiness, MIN(sh.InHour*60+InMinute) AS InTime_Mins, MAX(sh.OutHour*60+OutMinute) AS OutTime_Mins, SUM(((sh.outhour+case when sh.OutHour < sh.InHour then 24 else 0 end)*60 + sh.outminute) - (sh.inhour*60 + sh.inminute)) AS MinsWorked from #shift sh group by sh.FKStoreId,sh.FKEmployeeNumber, sh.DateOfBusiness ) AS derived order by FKEmployeeNumber, DateOfBusiness 
Rasmus Dybkjær
2#
Rasmus Dybkjær Reply to 2017-12-07 06:59:17Z
 I've written a sample of how you could do it. Briefly summarized I perform these steps: Mark records that should be grouped with either the next or the previous record, because they are a part of the same shift. Find the start and end of each shift Join all records for an employee that fall within each shift Group the resultset per employee per shift, and calculate the minutes worked by summing up the minutes between checkin and checkout for each record within a shift. Sample query: WITH ShiftParts AS ( SELECT FKEmployeeNumber, DATEADD(MINUTE, InMinute, DATEADD(HOUR, InHour, DateOfBusiness)) CheckIn, --Datetime of the checkin DATEADD(DAY, CASE WHEN OutHour < InHour THEN 1 ELSE 0 END, DATEADD(MINUTE, OutMinute, DATEADD(HOUR, OutHour, DateOfBusiness))) CheckOut --Datetime of the checkout (add one day if we crossed midnight). FROM #Shift ), GroupInfo AS ( SELECT *, CASE WHEN DATEDIFF(MINUTE, LAG(CheckOut, 1, NULL) OVER (PARTITION BY FKEmployeeNumber ORDER BY CheckOut), CheckIn) <= 120 THEN 1 ELSE 0 END AS GroupWithPrevious, --Determine whether we want to group this record with the previous CASE WHEN DATEDIFF(MINUTE, CheckOut, LEAD(CheckIn, 1, NULL) OVER (PARTITION BY FKEmployeeNumber ORDER BY CheckOut)) <= 120 THEN 1 ELSE 0 END AS GroupWithNext --Determine whether we want to group this record with the next FROM ShiftParts ), ShiftStartAndEnd AS ( SELECT *, CASE WHEN GroupWithNext = 1 THEN LEAD(CheckOut, 1, NULL) OVER (PARTITION BY FKEmployeeNumber ORDER BY CheckOut) ELSE CheckOut END AS FinalCheckOut FROM GroupInfo WHERE GroupWithPrevious = 0 OR GroupWithNext = 0 --Only pick beginning and end of a shift ) SELECT sse.FKEmployeeNumber, sse.CheckIn, sse.FinalCheckOut, SUM(DATEDIFF(MINUTE,sp.CheckIn,sp.CheckOut)) AS MinutesWorked FROM ShiftStartAndEnd sse LEFT OUTER JOIN ShiftParts sp ON sp.FKEmployeeNumber = sse.FKEmployeeNumber AND sp.CheckIn >= sse.CheckIn AND sp.CheckOut <= sse.FinalCheckOut WHERE sse.GroupWithPrevious = 0 GROUP BY sse.FKEmployeeNumber, sse.CheckIn, sse.FinalCheckOut  Note: I think the last two records in your sample data should not be grouped (26291, '12/05/2017', 3118, 1, 200, 23, 15, 5, 5), (26291, '12/06/2017', 3118, 0, 200, 15, 14, 7, 5) -- 11:15PM 7:05AM  should be: (26291, '12/05/2017', 3118, 1, 200, 23, 15, 5, 5), -- 11:19PM 5:05AM (26291, '12/06/2017', 3118, 0, 200, 15, 14, 7, 5) -- 3:14PM 7:05AM 
 You need to login account before you can post.
Processed in 0.302355 second(s) , Gzip On .