SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

6 rows affected

/* Generate Intervals */
DECLARE @StartDate AS datetimeoffset = '2022-09-19 00:00:00',
		@EndDate AS datetimeoffset = '2022-09-20 00:00:00';
DECLARE @NumberOfIntervals AS int = DATEDIFF(minute, @StartDate, @EndDate) / 30;

WITH L0 AS 
( 
	SELECT 1 AS c 
	FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),
                (1),(1),(1),(1),(1),(1),(1),(1)) AS D(c)
),
L1 AS ( SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B ),
L2 AS ( SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B ),
L3 AS ( SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B ),
Nums AS ( SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS rownum FROM L3 ),
Intervals AS 
(
	SELECT TOP(@NumberOfIntervals) 
		IntervalNumber = rownum,
		IntervalStart = DATEADD(minute, 30 * (rownum - 1), @StartDate),
		IntervalEndExclusive = DATEADD(minute, 30 * (rownum), @StartDate)
	FROM Nums
	ORDER BY rownum
)
SELECT *
FROM Intervals i
/* Spread the data across the intervals */
CROSS APPLY
(
	SELECT
		WorkLogsStarted = COUNT
		(
			CASE WHEN  wl.StartTime >= i2.IntervalStart AND wl.StartTime < i2.IntervalEndExclusive 
			THEN wl.Id
			ELSE NULL END
		),
		WorkDuration = SUM
		(
			CASE 
				/* This log fills the whole interval */
				WHEN wl.StartTime <= i2.IntervalStart AND DATEADD(minute, wl.DurationInMinutes, wl.StartTime) >= i2.IntervalEndExclusive 
				THEN DATEDIFF(minute, i2.IntervalStart, i2.IntervalEndExclusive)
				/* This log starts and ends inside the interval */
				WHEN wl.StartTime >= i2.IntervalStart AND DATEADD(minute, wl.DurationInMinutes, wl.StartTime) <= i2.IntervalEndExclusive 
				THEN wl.DurationInMinutes
				/* This log starts in the interval, but ends in a later interval, so we do IntervalEnd - StartTime */
				WHEN i2.IntervalEndExclusive < DATEADD(minute, wl.DurationInMinutes, wl.StartTime) 
				THEN DATEDIFF(minute,  wl.StartTime, i2.IntervalEndExclusive)
				/* This log ends in the interval, but didn't start here, so we do EndTime - IntervalStart */
				WHEN i2.IntervalEndExclusive > DATEADD(minute, wl.DurationInMinutes, wl.StartTime) 
				THEN DATEDIFF(minute,  i2.IntervalStart, DATEADD(minute, wl.DurationInMinutes, wl.StartTime))
				ELSE 0
			END
		)
	FROM WorkLog wl
	INNER JOIN Intervals i2
		ON i2.IntervalNumber = i.IntervalNumber
	WHERE 
		i.IntervalStart <= DATEADD(minute, wl.DurationInMinutes, wl.StartTime) 
		AND i.IntervalEndExclusive >= wl.StartTime
			
) AS TheData
ORDER BY i.IntervalStart;

IntervalNumber	IntervalStart	IntervalEndExclusive	WorkLogsStarted	WorkDuration
1	2022-09-19 00:00:00.0000000 +00:00	2022-09-19 00:30:00.0000000 +00:00	0	null
2	2022-09-19 00:30:00.0000000 +00:00	2022-09-19 01:00:00.0000000 +00:00	0	null
3	2022-09-19 01:00:00.0000000 +00:00	2022-09-19 01:30:00.0000000 +00:00	0	null
4	2022-09-19 01:30:00.0000000 +00:00	2022-09-19 02:00:00.0000000 +00:00	0	null
5	2022-09-19 02:00:00.0000000 +00:00	2022-09-19 02:30:00.0000000 +00:00	0	null
6	2022-09-19 02:30:00.0000000 +00:00	2022-09-19 03:00:00.0000000 +00:00	0	null
7	2022-09-19 03:00:00.0000000 +00:00	2022-09-19 03:30:00.0000000 +00:00	0	null
8	2022-09-19 03:30:00.0000000 +00:00	2022-09-19 04:00:00.0000000 +00:00	0	null
9	2022-09-19 04:00:00.0000000 +00:00	2022-09-19 04:30:00.0000000 +00:00	0	null
10	2022-09-19 04:30:00.0000000 +00:00	2022-09-19 05:00:00.0000000 +00:00	0	null
11	2022-09-19 05:00:00.0000000 +00:00	2022-09-19 05:30:00.0000000 +00:00	0	null
12	2022-09-19 05:30:00.0000000 +00:00	2022-09-19 06:00:00.0000000 +00:00	0	null
13	2022-09-19 06:00:00.0000000 +00:00	2022-09-19 06:30:00.0000000 +00:00	0	null
14	2022-09-19 06:30:00.0000000 +00:00	2022-09-19 07:00:00.0000000 +00:00	0	null
15	2022-09-19 07:00:00.0000000 +00:00	2022-09-19 07:30:00.0000000 +00:00	0	null
16	2022-09-19 07:30:00.0000000 +00:00	2022-09-19 08:00:00.0000000 +00:00	0	null
17	2022-09-19 08:00:00.0000000 +00:00	2022-09-19 08:30:00.0000000 +00:00	1	15
18	2022-09-19 08:30:00.0000000 +00:00	2022-09-19 09:00:00.0000000 +00:00	0	15
19	2022-09-19 09:00:00.0000000 +00:00	2022-09-19 09:30:00.0000000 +00:00	0	null
20	2022-09-19 09:30:00.0000000 +00:00	2022-09-19 10:00:00.0000000 +00:00	0	null
21	2022-09-19 10:00:00.0000000 +00:00	2022-09-19 10:30:00.0000000 +00:00	1	15
22	2022-09-19 10:30:00.0000000 +00:00	2022-09-19 11:00:00.0000000 +00:00	0	30
23	2022-09-19 11:00:00.0000000 +00:00	2022-09-19 11:30:00.0000000 +00:00	0	15
24	2022-09-19 11:30:00.0000000 +00:00	2022-09-19 12:00:00.0000000 +00:00	0	null
25	2022-09-19 12:00:00.0000000 +00:00	2022-09-19 12:30:00.0000000 +00:00	0	null
26	2022-09-19 12:30:00.0000000 +00:00	2022-09-19 13:00:00.0000000 +00:00	0	0
27	2022-09-19 13:00:00.0000000 +00:00	2022-09-19 13:30:00.0000000 +00:00	1	30
28	2022-09-19 13:30:00.0000000 +00:00	2022-09-19 14:00:00.0000000 +00:00	0	0
29	2022-09-19 14:00:00.0000000 +00:00	2022-09-19 14:30:00.0000000 +00:00	0	0
30	2022-09-19 14:30:00.0000000 +00:00	2022-09-19 15:00:00.0000000 +00:00	1	15
31	2022-09-19 15:00:00.0000000 +00:00	2022-09-19 15:30:00.0000000 +00:00	0	null
32	2022-09-19 15:30:00.0000000 +00:00	2022-09-19 16:00:00.0000000 +00:00	1	10
33	2022-09-19 16:00:00.0000000 +00:00	2022-09-19 16:30:00.0000000 +00:00	0	null
34	2022-09-19 16:30:00.0000000 +00:00	2022-09-19 17:00:00.0000000 +00:00	1	7
35	2022-09-19 17:00:00.0000000 +00:00	2022-09-19 17:30:00.0000000 +00:00	0	0
36	2022-09-19 17:30:00.0000000 +00:00	2022-09-19 18:00:00.0000000 +00:00	0	null
37	2022-09-19 18:00:00.0000000 +00:00	2022-09-19 18:30:00.0000000 +00:00	0	null
38	2022-09-19 18:30:00.0000000 +00:00	2022-09-19 19:00:00.0000000 +00:00	0	null
39	2022-09-19 19:00:00.0000000 +00:00	2022-09-19 19:30:00.0000000 +00:00	0	null
40	2022-09-19 19:30:00.0000000 +00:00	2022-09-19 20:00:00.0000000 +00:00	0	null
41	2022-09-19 20:00:00.0000000 +00:00	2022-09-19 20:30:00.0000000 +00:00	0	null
42	2022-09-19 20:30:00.0000000 +00:00	2022-09-19 21:00:00.0000000 +00:00	0	null
43	2022-09-19 21:00:00.0000000 +00:00	2022-09-19 21:30:00.0000000 +00:00	0	null
44	2022-09-19 21:30:00.0000000 +00:00	2022-09-19 22:00:00.0000000 +00:00	0	null
45	2022-09-19 22:00:00.0000000 +00:00	2022-09-19 22:30:00.0000000 +00:00	0	null
46	2022-09-19 22:30:00.0000000 +00:00	2022-09-19 23:00:00.0000000 +00:00	0	null
47	2022-09-19 23:00:00.0000000 +00:00	2022-09-19 23:30:00.0000000 +00:00	0	null
48	2022-09-19 23:30:00.0000000 +00:00	2022-09-20 00:00:00.0000000 +00:00	0	null

Warning: Null value is eliminated by an aggregate or other SET operation.