Home List Hours worked with multiple shifts and across dates
Reply: 1

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:

  1. Mark records that should be grouped with either the next or the previous record, because they are a part of the same shift.
  2. Find the start and end of each shift
  3. Join all records for an employee that fall within each shift
  4. 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.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.302355 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO