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

List Hours worked with multiple shifts and across dates

user4009
1#
user4009 Published in July 17, 2018, 7:58 am

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
You need to login account before you can post.

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

© 2016 Powered by mzan.com design MATCHINFO