By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table tbl
(
TechnicianID int,
StartTime datetime,
EndTime datetime,
TotalDurationSeconds int
)
insert into tbl values
(1, '2023-10-01 05:00:00', '2023-10-01 05:10:00', 600),
(1, '2023-10-01 05:10:00', '2023-10-01 05:25:00', 900),
(1, '2023-10-01 06:00:00', '2023-10-01 06:45:00', 2700),
(2, '2023-10-01 05:05:00', '2023-10-01 05:20:00', 900),
(2, '2023-10-01 05:30:00', '2023-10-01 05:35:00', 300),
(2, '2023-10-01 07:00:00', '2023-10-01 07:50:00', 3000);
6 rows affected
-- using recursive CTE to generate a numbers/tally table
-- Or use GENERATE_SERIES (SQL Server 2022+) on the fly
-- For performance, use a permanant numbers table
with numbers as
(
select n = 0
union all
select n = n + 1
from numbers
where n < 99 --assumed max 99. Change accordingly
),
cte as
(
select *
from tbl t
cross apply
(
-- Start & End Time in 15 mins block HH:00, HH:15, HH:30, HH:45
select StartB = dateadd(minute, datediff(minute, 0, StartTime) / 15 * 15, 0),
EndB = dateadd(minute, ceiling(datediff(minute, 0, EndTime) / 15.0) * 15, 0)
) blk
cross apply
(
-- calculate no of 15 mins block
select blocks = datediff(minute, StartB, EndB) / 15
) b
inner join numbers n on n.n >= 1
and n.n <= b.blocks
cross apply
(
select BlockStart = dateadd(minute, (n.n - 1) * 15, StartB),
BLockEnd = dateadd(minute, n.n * 15, StartB)
) bt
cross apply
(
select r = case when b.blocks = 1
| TechnicianID | StartTime | EndTime | TotalDurationSeconds | StartB | EndB | blocks | n | BlockStart | BLockEnd | r | ProductiveSeconds |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | 2023-10-01 05:00:00.000 | 2023-10-01 05:10:00.000 | 600 | 2023-10-01 05:00:00.000 | 2023-10-01 05:15:00.000 | 1 | 1 | 2023-10-01 05:00:00.000 | 2023-10-01 05:15:00.000 | 1.000000000000 | 600.00 |
| 1 | 2023-10-01 05:10:00.000 | 2023-10-01 05:25:00.000 | 900 | 2023-10-01 05:00:00.000 | 2023-10-01 05:30:00.000 | 2 | 1 | 2023-10-01 05:00:00.000 | 2023-10-01 05:15:00.000 | 0.333333333333 | 300.00 |
| 1 | 2023-10-01 05:10:00.000 | 2023-10-01 05:25:00.000 | 900 | 2023-10-01 05:00:00.000 | 2023-10-01 05:30:00.000 | 2 | 2 | 2023-10-01 05:15:00.000 | 2023-10-01 05:30:00.000 | 0.666666666666 | 600.00 |
| 1 | 2023-10-01 06:00:00.000 | 2023-10-01 06:45:00.000 | 2700 | 2023-10-01 06:00:00.000 | 2023-10-01 06:45:00.000 | 3 | 1 | 2023-10-01 06:00:00.000 | 2023-10-01 06:15:00.000 | 0.333333333333 | 900.00 |
| 1 | 2023-10-01 06:00:00.000 | 2023-10-01 06:45:00.000 | 2700 | 2023-10-01 06:00:00.000 | 2023-10-01 06:45:00.000 | 3 | 2 | 2023-10-01 06:15:00.000 | 2023-10-01 06:30:00.000 | 0.333333333333 | 900.00 |
| 1 | 2023-10-01 06:00:00.000 | 2023-10-01 06:45:00.000 | 2700 | 2023-10-01 06:00:00.000 | 2023-10-01 06:45:00.000 | 3 | 3 | 2023-10-01 06:30:00.000 | 2023-10-01 06:45:00.000 | 0.333333333333 | 900.00 |
| 2 | 2023-10-01 05:05:00.000 | 2023-10-01 05:20:00.000 | 900 | 2023-10-01 05:00:00.000 | 2023-10-01 05:30:00.000 | 2 | 1 | 2023-10-01 05:00:00.000 | 2023-10-01 05:15:00.000 | 0.666666666666 | 600.00 |
| 2 | 2023-10-01 05:05:00.000 | 2023-10-01 05:20:00.000 | 900 | 2023-10-01 05:00:00.000 | 2023-10-01 05:30:00.000 | 2 | 2 | 2023-10-01 05:15:00.000 | 2023-10-01 05:30:00.000 | 0.333333333333 | 300.00 |
| 2 | 2023-10-01 05:30:00.000 | 2023-10-01 05:35:00.000 | 300 | 2023-10-01 05:30:00.000 | 2023-10-01 05:45:00.000 | 1 | 1 | 2023-10-01 05:30:00.000 | 2023-10-01 05:45:00.000 | 1.000000000000 | 300.00 |
| 2 | 2023-10-01 07:00:00.000 | 2023-10-01 07:50:00.000 | 3000 | 2023-10-01 07:00:00.000 | 2023-10-01 08:00:00.000 | 4 | 1 | 2023-10-01 07:00:00.000 | 2023-10-01 07:15:00.000 | 0.300000000000 | 900.00 |
| 2 | 2023-10-01 07:00:00.000 | 2023-10-01 07:50:00.000 | 3000 | 2023-10-01 07:00:00.000 | 2023-10-01 08:00:00.000 | 4 | 2 | 2023-10-01 07:15:00.000 | 2023-10-01 07:30:00.000 | 0.300000000000 | 900.00 |
| 2 | 2023-10-01 07:00:00.000 | 2023-10-01 07:50:00.000 | 3000 | 2023-10-01 07:00:00.000 | 2023-10-01 08:00:00.000 | 4 | 3 | 2023-10-01 07:30:00.000 | 2023-10-01 07:45:00.000 | 0.300000000000 | 900.00 |
| 2 | 2023-10-01 07:00:00.000 | 2023-10-01 07:50:00.000 | 3000 | 2023-10-01 07:00:00.000 | 2023-10-01 08:00:00.000 | 4 | 4 | 2023-10-01 07:45:00.000 | 2023-10-01 08:00:00.000 | 0.100000000000 | 300.00 |