Assuming Microsoft SQL Server 2022, something like this should work:
WITH cteTimeSlots (SlotStart, SlotEnd) As
(
SELECT
Convert(time, '00:00'),
Convert(time, '00:30')
UNION ALL
SELECT
DateAdd(minute, 30, SlotStart),
DateAdd(minute, 30, SlotEnd)
FROM
cteTimeSlots
WHERE
SlotStart < '23:30'
)
SELECT
S.Date,
S.FIRSTNAME,
S.LASTNAME,
S.ID,
S.ACTIVITY,
T.SlotStart,
T.SlotEnd,
DateDiff(minute, GREATEST(T.SlotStart, S.START_EST), LEAST(T.SlotEnd, S.END_EST)) As Duration
FROM
#TMPSHRINK As S
INNER JOIN cteTimeSlots As T
ON T.SlotEnd > S.START_EST
AND T.SlotStart < S.END_EST
;
For earlier versions of SQL Server, replace
GREATEST
/
LEAST
with a
CASE
statement:
DateDiff(minute, CASE WHEN T.SlotStart > S.START_EST THEN T.SlotStart ELSE S.START_EST END, CASE WHEN T.SlotEnd < S.END_EST THEN T.SlotEnd ELSE S.END_EST END) As Duration