SQL Server take a time duration and parse the time into the hour parts of that duration

I am posting this question again because the project has changed and the previous answers don't return the desired results. Ambulances and fire trucks have the dispatch time when an emergency occurred and an end time for when the emergency was declared over.

Event 1 starts on May 1, 2021 10:17:33 and ends at may 1, 2021 10:33:41.

Event 2 starts on May 1, 2021 11:50:52 and ends at May 1, 2021 13:18:21.

I would like to parse the amount of time from the start to the end and place it into the hour parts when it occurs. For example; Event 1 starts at 10:17 and ends at 10:33. It would place 16 minutes minutes in the 10:00 hour part for that day. Event 2 would place 10 minutes in the 11:00 hour part, 60 minutes in the 12:00 hour part and 18 minutes in the 13:00 hour part. Place the minutes in the hours during which the event occured.

The results should look the following. Although I am flexible. For example, if the name of the truck cannot be returned in the results that would be ok because if the EventID is there, I could relate back to the original table.

The following SQL code comes close to working to deliver the right result. But it does not overlap days. There are many emergency events that start at 2300 hours and last until 0300 hours the following day.

DECLARE @tempFireEvents TABLE
(
  EventID INT NOT NULL
, Apparatus VARCHAR(10) NOT NULL
, StartDateTime DATETIME NOT NULL
, EndDateTime DATETIME NOT NULL
, DurationInSeconds INT NOT NULL
)

INSERT INTO @tempFireEvents
VALUES
  (1, 'Medic10', 'may 1, 2021 10:17:33', 'may 1, 2021 10:33:41', 968) /*This event is entirely within 1000 hours*/
, (2, 'Ladder73', 'may 1, 2021 11:50:52', 'may 1, 2021 13:18:21', 5249) /*This event spans 1100, 1200 and 1300 hours*/
, (3, 'Engine41', 'may 1, 2021 13:27:17', 'may 1, 2021 14:21:18', 3241) /*This event overlaps 1300 and 1400 hours*/
, (4, 'Medic83', 'may 1, 2021 15:28:08', 'may 1, 2021 16:05:48', 2260) /*This event overlaps 1500 and 1600 hours*/
, (5, 'Rescue32', 'may 1, 2021 16:20:43', 'may 1, 2021 16:53:28', 1965) /*This event is entirely within the 1600 hour part*/
, (6, 'Medic09', 'may 1, 2021 23:44:06', 'may 2, 2021 00:39:52', 3346) /*Notice this overlaps the 2300 and 0000 hours into the following day*/
, (7, 'Engine18', 'may 1, 2021 23:32:58', 'may 2, 2021 01:34:13', 7275) /*Notice this overlaps the 2300, 0000 and 0100 hours into the following day*/
, (8, 'Rescue63', 'may 2, 2021 00:17:45', 'may 2, 2021 00:52:09', 2064) /*Notice this is the 00 hour of the day and does not show in the results*/
;

WITH AllHours AS
(
SELECT 1 AS hourInt
UNION ALL
SELECT hourInt + 1
FROM AllHours
WHERE hourInt < 23
)
,
Combined AS
(
SELECT 
  T.EventID
, H.hourInt
, CASE WHEN DATEPART(HOUR, T.StartDateTime) = H.hourInt THEN 1 ELSE 0 END AS isStart
, CASE WHEN DATEPART(HOUR, T.EndDateTime) = H.hourInt THEN 1 ELSE 0 END AS isEnd
, T.StartDateTime
, T.EndDateTime
FROM @tempFireEvents AS [T]
JOIN AllHours AS [H] ON H.hourInt BETWEEN DATEPART(HOUR, T.StartDateTime) AND DATEPART(HOUR,T.EndDateTime)
)

SELECT 
  EventID
, hourInt
, CASE WHEN isStart = 1 AND isEnd = 0 THEN 60 - DATEPART(MINUTE, StartDateTime)
  WHEN isStart = 0 AND isEnd = 1 THEN DATEPART(MINUTE, EndDateTime)
  WHEN isStart = 1 AND isEnd = 1 THEN DATEPART(MINUTE, EndDateTime) -  DATEPART(MINUTE, StartDateTime)
  ELSE 60
  END AS MinutesInThisHour
FROM Combined
ORDER BY EventID ASC, hourint ASC
;

I have a suspiction that SQL Server may not be the best method to achieve the goal. It may need to be written in Python with increment and decrement and counters.

If it helps, I have a calendar table that looks like:

May 1, 2021 00:00:00
May 1, 2021 01:00:00
May 1, 2021 02:00:00
May 1, 2021 03:00:00
May 1, 2021 04:00:00
May 1, 2021 05:00:00
May 1, 2021 06:00:00

Would a calendar table be useful in solving this problem?

…

CREATE TABLE tempFireEvents
(
EventID VARCHAR(8) NOT NULL,
StartDateTime DATETIME NOT NULL,
EndDateTime DATETIME NOT NULL
)

INSERT INTO tempFireEvents
VALUES
('fire0001', 'november 1, 2018 10:45:00', 'november 2, 2018 11:30:00'),
('fire0002', 'november 1, 2018 11:50:00', 'november 1, 2018 13:10:00'),
('fire0003', 'november 1, 2018 13:20:00', 'november 1, 2018 14:20:00'),
('fire0004', 'november 1, 2018 15:25:00', 'november 1, 2018 16:05:00'),
('fire0005', 'november 1, 2018 16:20:00', 'november 2, 2018 17:00:00'),
('fire0006', 'november 1, 2018 16:20:00', 'november 1, 2018 17:01:00');

select e.*, hr.ld, 
   60 - case when e.startdatetime > hr.ld then datepart(minute, e.startdatetime) else 0 end
   + case when e.enddatetime < hr.ud then datepart(minute, e.enddatetime)-60 else 0 end as allocatedminutes
from tempFireEvents as e
cross apply
(
  select
    dateadd(hour, datepart(hour,e.startdatetime)+t.rn-1, cast(cast(e.startdatetime as date) as datetime)) as ld, 
    dateadd(hour, datepart(hour,e.startdatetime)+t.rn, cast(cast(e.startdatetime as date) as datetime)) as ud,
    rn
  from
  (
    -- a tally, max 100 rows .. max 100 hours duration
    select top (1+datediff(hour,e.startdatetime,dateadd(minute, -1, e.enddatetime))) row_number() over(order by @@spid) as rn
    from (values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) as a(n)
    cross join (values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) as b(n)
  ) as t
) as hr;