CREATE TABLE Tasks (

TaskID INT PRIMARY KEY,

Technician NVARCHAR(50),

StartDate DATETIME,

EndDate DATETIME

);

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');

WITH TimeIntervals AS (

SELECT

Technician,

TaskID,

StartDate,

EndDate,

DATEADD(MINUTE, (DATEDIFF(MINUTE, 0, StartDate) / 15) * 15, 0) AS IntervalStart,

DATEADD(MINUTE, ((DATEDIFF(MINUTE, 0, StartDate) / 15) + 1) * 15, 0) AS IntervalEnd

FROM

Tasks

UNION ALL

SELECT

Technician,

TaskID,

StartDate,

EndDate,

DATEADD(MINUTE, 15, IntervalStart),

DATEADD(MINUTE, 15, IntervalEnd)

FROM

TimeIntervals

WHERE

IntervalEnd < EndDate

)

, WorkIntervals AS (

SELECT

Technician,

TaskID,

IntervalStart,

IntervalEnd,

CASE

WHEN StartDate > IntervalStart THEN StartDate

ELSE IntervalStart

END AS WorkStart,

CASE

WHEN EndDate < IntervalEnd THEN EndDate

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

;

FROM #TimeBlocks;

SELECT t.*

, tb.*

FROM Tasks t

INNER JOIN #TimeBlocks tb

ON ( t.StartDate < tb.TimeBlockEnd

AND t.EndDate > tb.TimeBlockStart

);

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

);

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

;