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