SQL Server 2019 (Linux)

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 i.*,
		WorkLogsStarted = COUNT( WorkLogsStarted ),

WorkDuration2 = SUM(CASE WHEN diff >= 0 THEN diff ELSE 0 END)
FROM Intervals i
/* Spread the data across the intervals */
OUTER APPLY 
  ( SELECT
	    WorkLogsStarted = 
            CASE WHEN wl.StartTime >= i.IntervalStart 
                  AND wl.StartTime < i.IntervalEndExclusive
			    THEN wl.Id
			END,
        DATEDIFF(minute, op.StartTime, op.EndTime) AS diff
    FROM WorkLog wl
    OUTER APPLY
      ( SELECT
            EndTime = DATEADD(minute, wl.DurationInMinutes, wl.StartTime)
      ) AS w
    OUTER APPLY
      ( SELECT 
            StartTime =   -- GREATEST( of the two start times )
                CASE WHEN wl.StartTime < i.IntervalStart
                    THEN i.IntervalStart ELSE wl.StartTime
                END,
            EndTime =   -- LEAST( of the two end times )
                CASE WHEN w.EndTime < i.IntervalEndExclusive
                    THEN w.EndTime ELSE i.IntervalEndExclusive
                END
      ) AS op
  ) AS oo
GROUP BY i.IntervalNumber, i.IntervalStart, i.IntervalEndExclusive
ORDER BY i.IntervalStart;

IntervalNumber	IntervalStart	IntervalEndExclusive	WorkLogsStarted	WorkDuration2
1	2022-09-19 00:00:00.0000000 +00:00	2022-09-19 00:30:00.0000000 +00:00	0	0
2	2022-09-19 00:30:00.0000000 +00:00	2022-09-19 01:00:00.0000000 +00:00	0	0
3	2022-09-19 01:00:00.0000000 +00:00	2022-09-19 01:30:00.0000000 +00:00	0	0
4	2022-09-19 01:30:00.0000000 +00:00	2022-09-19 02:00:00.0000000 +00:00	0	0
5	2022-09-19 02:00:00.0000000 +00:00	2022-09-19 02:30:00.0000000 +00:00	0	0
6	2022-09-19 02:30:00.0000000 +00:00	2022-09-19 03:00:00.0000000 +00:00	0	0
7	2022-09-19 03:00:00.0000000 +00:00	2022-09-19 03:30:00.0000000 +00:00	0	0
8	2022-09-19 03:30:00.0000000 +00:00	2022-09-19 04:00:00.0000000 +00:00	0	0
9	2022-09-19 04:00:00.0000000 +00:00	2022-09-19 04:30:00.0000000 +00:00	0	0
10	2022-09-19 04:30:00.0000000 +00:00	2022-09-19 05:00:00.0000000 +00:00	0	0
11	2022-09-19 05:00:00.0000000 +00:00	2022-09-19 05:30:00.0000000 +00:00	0	0
12	2022-09-19 05:30:00.0000000 +00:00	2022-09-19 06:00:00.0000000 +00:00	0	0
13	2022-09-19 06:00:00.0000000 +00:00	2022-09-19 06:30:00.0000000 +00:00	0	0
14	2022-09-19 06:30:00.0000000 +00:00	2022-09-19 07:00:00.0000000 +00:00	0	0
15	2022-09-19 07:00:00.0000000 +00:00	2022-09-19 07:30:00.0000000 +00:00	0	0
16	2022-09-19 07:30:00.0000000 +00:00	2022-09-19 08:00:00.0000000 +00:00	0	0
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	0
20	2022-09-19 09:30:00.0000000 +00:00	2022-09-19 10:00:00.0000000 +00:00	0	0
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	0
25	2022-09-19 12:00:00.0000000 +00:00	2022-09-19 12:30:00.0000000 +00:00	0	0
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	0
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	0
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	0
37	2022-09-19 18:00:00.0000000 +00:00	2022-09-19 18:30:00.0000000 +00:00	0	0
38	2022-09-19 18:30:00.0000000 +00:00	2022-09-19 19:00:00.0000000 +00:00	0	0
39	2022-09-19 19:00:00.0000000 +00:00	2022-09-19 19:30:00.0000000 +00:00	0	0
40	2022-09-19 19:30:00.0000000 +00:00	2022-09-19 20:00:00.0000000 +00:00	0	0
41	2022-09-19 20:00:00.0000000 +00:00	2022-09-19 20:30:00.0000000 +00:00	0	0
42	2022-09-19 20:30:00.0000000 +00:00	2022-09-19 21:00:00.0000000 +00:00	0	0
43	2022-09-19 21:00:00.0000000 +00:00	2022-09-19 21:30:00.0000000 +00:00	0	0
44	2022-09-19 21:30:00.0000000 +00:00	2022-09-19 22:00:00.0000000 +00:00	0	0
45	2022-09-19 22:00:00.0000000 +00:00	2022-09-19 22:30:00.0000000 +00:00	0	0
46	2022-09-19 22:30:00.0000000 +00:00	2022-09-19 23:00:00.0000000 +00:00	0	0
47	2022-09-19 23:00:00.0000000 +00:00	2022-09-19 23:30:00.0000000 +00:00	0	0
48	2022-09-19 23:30:00.0000000 +00:00	2022-09-20 00:00:00.0000000 +00:00	0	0

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