By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
-- Create the Tasks table
CREATE TABLE Tasks (
TaskID INT PRIMARY KEY,
Technician NVARCHAR(50),
StartDate DATETIME,
EndDate DATETIME
);
-- Insert sample data into the Tasks table
INSERT INTO Tasks (TaskID, Technician, StartDate, EndDate)
VALUES
(1, 'Tech1', '2023-10-30 08:05:00', '2023-10-30 08:25:00'),
(2, 'Tech1', '2023-10-30 08:20:00', '2023-10-30 08:35:00'),
(3, 'Tech2', '2023-10-30 08:30:00', '2023-10-30 09:10:00'),
(4, 'Tech1', '2023-10-30 09:05:00', '2023-10-30 09:45:00'),
(5, 'Tech3', '2023-10-30 08:10:00', '2023-10-30 08:20:00'),
(6, 'Tech3', '2023-10-30 08:30:00', '2023-10-30 08:45:00');
6 rows affected
-- Generate a Time Blocks table containing every 15 minutes since 2000-01-01.
-- If you have a numbers table going from 0 to 1million or so, use that and multiply each number by 15.
-- FifteenMinuteBlocks uses generate_series() to create that numbers table on the fly for us.
-- But generate_series() is only available starting with SQL Server 2022.
WITH FifteenMinuteBlocks AS
(
SELECT value * 15 AS Fifteens
FROM generate_series(0, 1000000) t
)
SELECT DATEADD(minute, b.Fifteens, '2000-01-01') AS TimeBlockStart
, DATEADD(minute, b.Fifteens + 15, '2000-01-01') AS TimeBlockEnd
INTO #TimeBlocks
FROM FifteenMinuteBlocks b
;
1000001 rows affected
SELECT TOP 10 *
FROM #TimeBlocks;
| TimeBlockStart | TimeBlockEnd |
|---|---|
| 2000-01-01 00:00:00.000 | 2000-01-01 00:15:00.000 |
| 2000-01-01 00:15:00.000 | 2000-01-01 00:30:00.000 |
| 2000-01-01 00:30:00.000 | 2000-01-01 00:45:00.000 |
| 2000-01-01 00:45:00.000 | 2000-01-01 01:00:00.000 |
| 2000-01-01 01:00:00.000 | 2000-01-01 01:15:00.000 |
| 2000-01-01 01:15:00.000 | 2000-01-01 01:30:00.000 |
| 2000-01-01 01:30:00.000 | 2000-01-01 01:45:00.000 |
| 2000-01-01 01:45:00.000 | 2000-01-01 02:00:00.000 |
| 2000-01-01 02:00:00.000 | 2000-01-01 02:15:00.000 |
| 2000-01-01 02:15:00.000 | 2000-01-01 02:30:00.000 |
-- Basically, we're just joining the TimeBlocks table to our data.
SELECT t.*
, tb.*
FROM Tasks t
INNER JOIN #TimeBlocks tb
ON ( t.StartDate < tb.TimeBlockEnd
AND t.EndDate > tb.TimeBlockStart
);
| TaskID | Technician | StartDate | EndDate | TimeBlockStart | TimeBlockEnd |
|---|---|---|---|---|---|
| 1 | Tech1 | 2023-10-30 08:05:00.000 | 2023-10-30 08:25:00.000 | 2023-10-30 08:00:00.000 | 2023-10-30 08:15:00.000 |
| 1 | Tech1 | 2023-10-30 08:05:00.000 | 2023-10-30 08:25:00.000 | 2023-10-30 08:15:00.000 | 2023-10-30 08:30:00.000 |
| 2 | Tech1 | 2023-10-30 08:20:00.000 | 2023-10-30 08:35:00.000 | 2023-10-30 08:15:00.000 | 2023-10-30 08:30:00.000 |
| 2 | Tech1 | 2023-10-30 08:20:00.000 | 2023-10-30 08:35:00.000 | 2023-10-30 08:30:00.000 | 2023-10-30 08:45:00.000 |
| 3 | Tech2 | 2023-10-30 08:30:00.000 | 2023-10-30 09:10:00.000 | 2023-10-30 08:30:00.000 | 2023-10-30 08:45:00.000 |
| 3 | Tech2 | 2023-10-30 08:30:00.000 | 2023-10-30 09:10:00.000 | 2023-10-30 08:45:00.000 | 2023-10-30 09:00:00.000 |
| 3 | Tech2 | 2023-10-30 08:30:00.000 | 2023-10-30 09:10:00.000 | 2023-10-30 09:00:00.000 | 2023-10-30 09:15:00.000 |
| 4 | Tech1 | 2023-10-30 09:05:00.000 | 2023-10-30 09:45:00.000 | 2023-10-30 09:00:00.000 | 2023-10-30 09:15:00.000 |
| 4 | Tech1 | 2023-10-30 09:05:00.000 | 2023-10-30 09:45:00.000 | 2023-10-30 09:15:00.000 | 2023-10-30 09:30:00.000 |
| 4 | Tech1 | 2023-10-30 09:05:00.000 | 2023-10-30 09:45:00.000 | 2023-10-30 09:30:00.000 | 2023-10-30 09:45:00.000 |
| 5 | Tech3 | 2023-10-30 08:10:00.000 | 2023-10-30 08:20:00.000 | 2023-10-30 08:00:00.000 | 2023-10-30 08:15:00.000 |
| 5 | Tech3 | 2023-10-30 08:10:00.000 | 2023-10-30 08:20:00.000 | 2023-10-30 08:15:00.000 | 2023-10-30 08:30:00.000 |
| 6 | Tech3 | 2023-10-30 08:30:00.000 | 2023-10-30 08:45:00.000 | 2023-10-30 08:30:00.000 | 2023-10-30 08:45:00.000 |
-- And then we need to calculate the number of minutes that are within our time block.
SELECT t.TaskID
, t.Technician
, DATEDIFF(minute
, IIF(t.StartDate < tb.TimeBlockStart, tb.TimeBlockStart, t.StartDate)
, IIF(t.EndDate > tb.TimeBlockEnd, tb.TimeBlockEnd, t.EndDate)
) AS Minutes
, t.StartDate
, t.EndDate
, tb.TimeBlockStart
, tb.TimeBlockEnd
FROM Tasks t
INNER JOIN #TimeBlocks tb
ON ( t.StartDate < tb.TimeBlockEnd
AND t.EndDate > tb.TimeBlockStart
);
| TaskID | Technician | Minutes | StartDate | EndDate | TimeBlockStart | TimeBlockEnd |
|---|---|---|---|---|---|---|
| 1 | Tech1 | 10 | 2023-10-30 08:05:00.000 | 2023-10-30 08:25:00.000 | 2023-10-30 08:00:00.000 | 2023-10-30 08:15:00.000 |
| 1 | Tech1 | 10 | 2023-10-30 08:05:00.000 | 2023-10-30 08:25:00.000 | 2023-10-30 08:15:00.000 | 2023-10-30 08:30:00.000 |
| 2 | Tech1 | 10 | 2023-10-30 08:20:00.000 | 2023-10-30 08:35:00.000 | 2023-10-30 08:15:00.000 | 2023-10-30 08:30:00.000 |
| 2 | Tech1 | 5 | 2023-10-30 08:20:00.000 | 2023-10-30 08:35:00.000 | 2023-10-30 08:30:00.000 | 2023-10-30 08:45:00.000 |
| 3 | Tech2 | 15 | 2023-10-30 08:30:00.000 | 2023-10-30 09:10:00.000 | 2023-10-30 08:30:00.000 | 2023-10-30 08:45:00.000 |
| 3 | Tech2 | 15 | 2023-10-30 08:30:00.000 | 2023-10-30 09:10:00.000 | 2023-10-30 08:45:00.000 | 2023-10-30 09:00:00.000 |
| 3 | Tech2 | 10 | 2023-10-30 08:30:00.000 | 2023-10-30 09:10:00.000 | 2023-10-30 09:00:00.000 | 2023-10-30 09:15:00.000 |
| 4 | Tech1 | 10 | 2023-10-30 09:05:00.000 | 2023-10-30 09:45:00.000 | 2023-10-30 09:00:00.000 | 2023-10-30 09:15:00.000 |
| 4 | Tech1 | 15 | 2023-10-30 09:05:00.000 | 2023-10-30 09:45:00.000 | 2023-10-30 09:15:00.000 | 2023-10-30 09:30:00.000 |
| 4 | Tech1 | 15 | 2023-10-30 09:05:00.000 | 2023-10-30 09:45:00.000 | 2023-10-30 09:30:00.000 | 2023-10-30 09:45:00.000 |
| 5 | Tech3 | 5 | 2023-10-30 08:10:00.000 | 2023-10-30 08:20:00.000 | 2023-10-30 08:00:00.000 | 2023-10-30 08:15:00.000 |
| 5 | Tech3 | 5 | 2023-10-30 08:10:00.000 | 2023-10-30 08:20:00.000 | 2023-10-30 08:15:00.000 | 2023-10-30 08:30:00.000 |
| 6 | Tech3 | 15 | 2023-10-30 08:30:00.000 | 2023-10-30 08:45:00.000 | 2023-10-30 08:30:00.000 | 2023-10-30 08:45:00.000 |
-- Then we SUM the minutes for each tech within the time block.
SELECT
t.Technician
, tb.TimeBlockStart
, SUM(DATEDIFF(minute
, IIF(t.StartDate < tb.TimeBlockStart, tb.TimeBlockStart, t.StartDate)
, IIF(t.EndDate > tb.TimeBlockEnd, tb.TimeBlockEnd, t.EndDate)
)) AS Minutes
FROM Tasks t
INNER JOIN #TimeBlocks tb
ON ( t.StartDate < tb.TimeBlockEnd
AND t.EndDate > tb.TimeBlockStart
)
GROUP BY t.Technician
, tb.TimeBlockStart
ORDER BY t.Technician
, tb.TimeBlockStart
;
| Technician | TimeBlockStart | Minutes |
|---|---|---|
| Tech1 | 2023-10-30 08:00:00.000 | 10 |
| Tech1 | 2023-10-30 08:15:00.000 | 20 |
| Tech1 | 2023-10-30 08:30:00.000 | 5 |
| Tech1 | 2023-10-30 09:00:00.000 | 10 |
| Tech1 | 2023-10-30 09:15:00.000 | 15 |
| Tech1 | 2023-10-30 09:30:00.000 | 15 |
| Tech2 | 2023-10-30 08:30:00.000 | 15 |
| Tech2 | 2023-10-30 08:45:00.000 | 15 |
| Tech2 | 2023-10-30 09:00:00.000 | 10 |
| Tech3 | 2023-10-30 08:00:00.000 | 5 |
| Tech3 | 2023-10-30 08:15:00.000 | 5 |
| Tech3 | 2023-10-30 08:30:00.000 | 15 |