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'
);
EmployeeId | TimeStartRaw | TimeEndRaw | ActualStartTime | ActualEndTime |
---|---|---|---|---|
1111 | 2023-09-10 07:00:07.000 | 2023-09-10 15:55:45.000 | 07:00:07.0000000 | 15: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
1条答案
按热度按时间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 :
Join that to your
EmployeePunch
table will give you the number or rows you want:This will give you the following table:
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
withTimeFromParts
to convertH
to time of day, and usingdateDiff
,Lead
andIsNull
to get the minutes per row:Note the
PARTITION BY
part of theLEAD
- I'm assuming your table contains more than one shift for more than one employee.You can see a live demo on db<>fiddle