Myujth

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;

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

SELECT EventID,
 DATEPART(hour, StartDateTime) AS HourOfDay,
 DATEDIFF(minute, StartDateTime, DATEADD(hour, DATEDIFF(hour, 0, StartDateTime) + 1, 0)) AS MinutesForThisHourPart
FROM tempFireEvents
UNION ALL
SELECT EventID,
 DATEPART(hour, EndDateTime) AS HourOfDay,
 DATEDIFF(minute, EndDateTime, DATEADD(hour, DATEDIFF(hour, 0, EndDateTime) + 1, 0)) AS MinutesForThisHourPart
FROM tempFireEvents
ORDER BY 1

Result

EventID HourOfDay MinutesForThisHourPart
-------- ----------- ----------------------
fire0001 10 15
fire0001 11 30
fire0002 11 10
fire0002 13 50
fire0003 13 40
fire0003 14 40
fire0004 15 35
fire0004 16 55
fire0005 16 10
fire0005 16 40

maybe something like this

select EventID,
 datepart(hour, StartDateTime) AS StartHour,
 datediff(minute, StartDateTime, dateadd(hour, datediff(hour, 0, StartDateTime) + 1, 0)) AS StartMinutes,
 datepart(hour, EndDateTime) AS EndHour,
 datediff(minute, EndDateTime, dateadd(hour, datediff(hour, 0, EndDateTime) + 1, 0)) AS EndMinutes
from tempFireEvents

this returns

EventID StartHour StartMinutes EndHour EndMinutes 
------- --------- ------------ ------- ---------- 
fire0001 10 15 11 30 
fire0002 11 10 13 50 
fire0003 13 40 14 40 
fire0004 15 35 16 55 
fire0005 16 40 16 10