SQL Server 2022

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

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

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