SQL Server Split date range to hours based on Shift Schedule

a9wyjsp7  于 12个月前  发布在  其他
关注(0)|答案(1)|浏览(119)

I'm trying to create a split breakdown of hours worked based on a shift date.
Technically we'll be using 2 dates, the start and end date (datetimes) to determine the per hour an employee worked.
Attached is the sample result that I need to display.

The duration of the shift is not fixed, some has 8hrs less, some has 8hrs more, so the number of records per shift date per employee is not fixed to a number of rows.

create table EmployeePunch 
(
    EmployeeId int, 
    TimeStartRaw datetime, 
    TimeEndRaw datetime, 
    ActualStartTime Time, 
    ActualEndTime Time
);
    
insert into EmployeePunch 
(
    EmployeeId, 
    TimeStartRaw, 
    TimeEndRaw, 
    ActualStartTime, 
    ActualEndTime
) values (
    11111,
    '2023-09-10 07:00:07.000',
    '2023-09-10 15:55:45.000',
    '07:00:07.0000000',
    '15:55:45.0000000'
);
EmployeeIdTimeStartRawTimeEndRawActualStartTimeActualEndTime
11112023-09-10 07:00:07.0002023-09-10 15:55:45.00007:00:07.000000015:55:45.0000000

With the sample above, I need to show the following:
| EmployeeId | TimeStartRaw | TimeEndRaw | HourOfDay | MinutesWorked |
| ------------ | ------------ | ------------ | ------------ | ------------ |
| 1111 | 2023-09-10 07:00:07.000 | 2023-09-10 15:55:45.000 | 7AM | 60 |
| 1111 | 2023-09-10 07:00:07.000 | 2023-09-10 15:55:45.000 | 8AM | 60 |
| 1111 | 2023-09-10 07:00:07.000 | 2023-09-10 15:55:45.000 | 9AM | 60 |
| 1111 | 2023-09-10 07:00:07.000 | 2023-09-10 15:55:45.000 | 10AM | 60 |
| 1111 | 2023-09-10 07:00:07.000 | 2023-09-10 15:55:45.000 | 11AM | 60 |
| 1111 | 2023-09-10 07:00:07.000 | 2023-09-10 15:55:45.000 | 12PM | 60 |
| 1111 | 2023-09-10 07:00:07.000 | 2023-09-10 15:55:45.000 | 13PM | 60 |
| 1111 | 2023-09-10 07:00:07.000 | 2023-09-10 15:55:45.000 | 14PM | 60 |
| 1111 | 2023-09-10 07:00:07.000 | 2023-09-10 15:55:45.000 | 15PM | 55 |

Your help is appreciated

gcmastyq

gcmastyq1#

To split the range to multiple rows, each represents an hour (or a part of an hour), you can join your table with a numbers table. If you don't already have a numbers table, you can use a common table expression instead. There are plenty of ways to do that, specifically since we're talking about only 24 rows, I would simply use a Table Value Constructor :

SELECT H
FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),
  (13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23),(24))V(H)

Join that to your EmployeePunch table will give you the number or rows you want:

WITH cte AS 
(
  SELECT H
  FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),
  (13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23),(24))V(H)
)
SELECT EmployeeId
  , TimeStartRaw
  , TimeEndRaw
  , H
FROM EmployeePunch
INNER JOIN cte
    ON DATEPART(HOUR, ActualStartTime) <= H
    AND DATEPART(HOUR, ActualEndTime) >= H

This will give you the following table:

EmployeeIdTimeStartRawTimeEndRawH
111112023-09-10 07:00:07.0002023-09-10 15:55:45.0007
111112023-09-10 07:00:07.0002023-09-10 15:55:45.0008
111112023-09-10 07:00:07.0002023-09-10 15:55:45.0009
111112023-09-10 07:00:07.0002023-09-10 15:55:45.00010
111112023-09-10 07:00:07.0002023-09-10 15:55:45.00011
111112023-09-10 07:00:07.0002023-09-10 15:55:45.00012
111112023-09-10 07:00:07.0002023-09-10 15:55:45.00013
111112023-09-10 07:00:07.0002023-09-10 15:55:45.00014
111112023-09-10 07:00:07.0002023-09-10 15:55:45.00015

Now there are a couple more tricks to do to get the time of day and calculate the number of minutes per row: Using cross apply with TimeFromParts to convert H to time of day, and using dateDiff , Lead and IsNull to get the minutes per row:

WITH cte AS 
(
  SELECT H
  FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),
  (13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23),(24))V(H)
)
SELECT EmployeeId
  , TimeStartRaw
  , TimeEndRaw
  , HourOfDay
  , DATEDIFF(MINUTE, HourOfDay, ISNULL(LEAD(HourOfDay) OVER(PARTITION BY EmployeeId, CAST(TimeStartRaw As Date) ORDER BY H), TimeEndRaw)) As MinutesWorked
FROM EmployeePunch
INNER JOIN cte
    ON DATEPART(HOUR, ActualStartTime) <= H
    AND DATEPART(HOUR, ActualEndTime) >= H
CROSS APPLY (SELECT TIMEFROMPARTS(H,0,0,0,0) As HourOfDay) As T
EmployeeIdTimeStartRawTimeEndRawHourOfDayMinutesWorked
111112023-09-10 07:00:07.0002023-09-10 15:55:45.00007:00:0060
111112023-09-10 07:00:07.0002023-09-10 15:55:45.00008:00:0060
111112023-09-10 07:00:07.0002023-09-10 15:55:45.00009:00:0060
111112023-09-10 07:00:07.0002023-09-10 15:55:45.00010:00:0060
111112023-09-10 07:00:07.0002023-09-10 15:55:45.00011:00:0060
111112023-09-10 07:00:07.0002023-09-10 15:55:45.00012:00:0060
111112023-09-10 07:00:07.0002023-09-10 15:55:45.00013:00:0060
111112023-09-10 07:00:07.0002023-09-10 15:55:45.00014:00:0060
111112023-09-10 07:00:07.0002023-09-10 15:55:45.00015:00:0055

Note the PARTITION BY part of the LEAD - I'm assuming your table contains more than one shift for more than one employee.

You can see a live demo on db<>fiddle

相关问题