SQL Server and allocate time into hour parts

Question

Background:

Ambulances and fire trucks have the dispatch time when an emergency occurred and a clear time for when the emergency was declared over.

For example: an emergency (EventID = fire0001) occurs at 10:45:00 and ends at 11:30:00.

Another emergency event (EventID = fire0002) starts at 11:50:00 and ends at 13:10:00

Question:

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; fire0001 starts at 10:45 and ends at 11:30.

I would like the results to show 15 minutes in the 10 hour part and 30 minutes in the 11 hour part.

eventID    HourOfDay    Minutes forThisHourPart
------------------------------------------------    
fire0001       10              15
fire0001       11              30

This information is useful for ambulance planning to determine the utilization for each hour of the day.

How can I calculate the amount of time spent per hour given a start time and an end time?.

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 1, 2108 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, 2108 14:20:00'),
    ('fire0004', 'november 1, 2018 15:25:00', 'november 1, 2018 16:05:00'),
    ('fire0005', 'november 1, 2018 16:20:00', 'november 1, 2018 16:50:00');


--SELECT EventID, StartDateTime, EndDateTime FROM tempFireEvents;

Answer 1

I believe this accomplishes what you want:

DECLARE @tempFireEvents TABLE
(
    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 1, 2108 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, 2108 14:20:00'),
    ('fire0004', 'november 1, 2018 15:25:00', 'november 1, 2018 16:05:00'),
    ('fire0005', 'november 1, 2018 16:20:00', 'november 1, 2018 16:50:00')

;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 T
        JOIN AllHours H ON H.hourInt BETWEEN DATEPART(HOUR,T.StartDateTime) AND DATEPART(HOUR,T.EndDateTime)
    )

--SELECT * FROM Combined

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 minutesForHour
FROM Combined

Output:

EventID     hourInt     minutesForHour
fire0001    10          15
fire0001    11          30
fire0002    11          10
fire0002    12          60
fire0002    13          10
fire0003    13          40
fire0003    14          20
fire0004    15          35
fire0004    16          5
fire0005    16          30

As mentioned in the comments, the way you are storing your EventID is far from optimal. A better approach is to assign a "type" to each event such as:

DECLARE @EventType TABLE 
(
    Id INT,
    EventType NVARCHAR(50)
)

INSERT INTO @EventType
VALUES
    (1,'Fire'),
    (2,'Public Awareness'),
    (3,'Cat in a Tree'),
    (4,'Motor Vehicle Accident')

DECLARE @tempFireEvents TABLE
(
    EventID INT IDENTITY (1,1) NOT NULL,
    EventTypeID INT NOT NULL,
    StartDateTime DATETIME NOT NULL,
    EndDateTime DATETIME NOT NULL
)

INSERT INTO @tempFireEvents (EventTypeID,StartDateTime,EndDateTime)
VALUES
    (1, 'november 1, 2018 10:45:00', 'november 1, 2108 11:30:00'),
    (2, 'november 1, 2018 11:50:00', 'november 1, 2018 13:10:00'),
    (4, 'november 1, 2018 13:20:00', 'november 1, 2108 14:20:00'),
    (1, 'november 1, 2018 15:25:00', 'november 1, 2018 16:05:00'),
    (3, 'november 1, 2018 16:20:00', 'november 1, 2018 16:50:00')

;WITH AllHours AS
(
    SELECT 1 AS hourInt
    UNION ALL
    SELECT hourInt+1
    FROM AllHours
    WHERE hourInt<23
), Combined AS
    (
        SELECT T.EventID,
               T.EventTypeID,
               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 T
        JOIN AllHours H ON H.hourInt BETWEEN DATEPART(HOUR,T.StartDateTime) AND DATEPART(HOUR,T.EndDateTime)
    )

--SELECT * FROM Combined

SELECT C.EventID,
       T.EventType,
       C.hourInt,
       CASE WHEN C.isStart=1 AND C.isEnd=0 THEN 60-DATEPART(MINUTE,C.StartDateTime)
            WHEN C.isStart=0 AND C.isEnd=1 THEN DATEPART(MINUTE,C.EndDateTime)
            WHEN C.isStart=1 AND C.isEnd=1 THEN DATEPART(MINUTE,C.EndDateTime)-DATEPART(MINUTE,C.StartDateTime)
            ELSE 60
       END AS minutesForHour
FROM Combined C
Join @EventType t ON C.EventTypeID=T.Id
ORDER BY C.EventID, C.hourInt

Output:

EventID EventType               hourInt minutesForHour
1       Fire                    10      15
1       Fire                    11      30
2       Public Awareness        11      10
2       Public Awareness        12      60
2       Public Awareness        13      10
3       Motor Vehicle Accident  13      40
3       Motor Vehicle Accident  14      20
4       Fire                    15      35
4       Fire                    16      5
5       Cat in a Tree           16      30