SQL Server and allocate time into hour parts
6
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;
sql
sql-server temporal edited Nov 15 '18 at 16:40
marc_s
579k12911181265
asked Nov 15 '18 at 16:20
David Fort MyersDavid Fort Myers
88211
add a comment |
6
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;
sql
sql-server temporal edited Nov 15 '18 at 16:40
marc_s
579k12911181265
asked Nov 15 '18 at 16:20
David Fort MyersDavid Fort Myers
88211
2
Sorry, but I cringe when I see your eventID column. Your eventID column is a horrible design. You should separate them into eventID and eventType. Also, please don't store "fire" as an event type on every row, use "F" and then FK to a new table that has eventType and eventDescription.
– KM.
Nov 15 '18 at 16:47
add a comment |
6
6
6
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;
sql
sql-server temporal edited Nov 15 '18 at 16:40
marc_s
579k12911181265
asked Nov 15 '18 at 16:20
David Fort MyersDavid Fort Myers
88211
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;
sql
sql-server temporal sql
sql-server temporal edited Nov 15 '18 at 16:40
marc_s
579k12911181265
asked Nov 15 '18 at 16:20
David Fort MyersDavid Fort Myers
88211
edited Nov 15 '18 at 16:40
marc_s
579k12911181265
asked Nov 15 '18 at 16:20
David Fort MyersDavid Fort Myers
88211
edited Nov 15 '18 at 16:40
marc_s
579k12911181265
edited Nov 15 '18 at 16:40
marc_s
579k12911181265
edited Nov 15 '18 at 16:40
marc_s
579k12911181265
579k12911181265
asked Nov 15 '18 at 16:20
David Fort MyersDavid Fort Myers
88211
asked Nov 15 '18 at 16:20
David Fort MyersDavid Fort Myers
88211
asked Nov 15 '18 at 16:20
David Fort MyersDavid Fort Myers
88211
88211
2
Sorry, but I cringe when I see your eventID column. Your eventID column is a horrible design. You should separate them into eventID and eventType. Also, please don't store "fire" as an event type on every row, use "F" and then FK to a new table that has eventType and eventDescription.
– KM.
Nov 15 '18 at 16:47
add a comment |
2
Sorry, but I cringe when I see your eventID column. Your eventID column is a horrible design. You should separate them into eventID and eventType. Also, please don't store "fire" as an event type on every row, use "F" and then FK to a new table that has eventType and eventDescription.
– KM.
Nov 15 '18 at 16:47
2
2
Sorry, but I cringe when I see your eventID column. Your eventID column is a horrible design. You should separate them into eventID and eventType. Also, please don't store "fire" as an event type on every row, use "F" and then FK to a new table that has eventType and eventDescription.
– KM.
Nov 15 '18 at 16:47
Sorry, but I cringe when I see your eventID column. Your eventID column is a horrible design. You should separate them into eventID and eventType. Also, please don't store "fire" as an event type on every row, use "F" and then FK to a new table that has eventType and eventDescription.
– KM.
Nov 15 '18 at 16:47
add a comment |
3 Answers
3
active
oldest
votes
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
answered Nov 15 '18 at 20:39
Dave CullumDave Cullum
6,16411329
Thank you Dave Collum. This works very well.
– David Fort Myers
Nov 19 '18 at 18:20
add a comment |
1
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
answered Nov 15 '18 at 16:41
sergeserge
69147
Hi Serge: This answer comes very close. However fire0002 does not include the span of the 12th hour which would be 60 minutes. I want to account for the time these fire trucks worked. Regarding fire0003 I needed to correct the EndDateTime (8th line of your query). It should have 60 - DATEDIFF(~~~~~~~) AS [MinutesForThisHourPart]. Your query returns 40 minutes remaining in the hour, but what I want to know is how many minutes this truck worked for that hour. Last: the fire0005 should return 30 minutes because it started at 16:20 and ended at 16:50.
– David Fort Myers
Nov 15 '18 at 18:30
add a comment |
1
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
answered Nov 15 '18 at 16:52
GuidoGGuidoG
5,52031844
Hi Guido: This answer comes very close. First issue is that fire0002 also spans the 12 hour and would put 60 minutes there. Also for fire0002 it ends at 13:10, therefore it would put 10 minutes in the 13th hour whereas you have 50. I modified the fifth line in your query to read "60 - ........ AS [EndMinutes] to correct this issue. A second issue would be fire0005. In this event there are 30 minutes within the 16th hour. This event starts at 16:20 and ends in the same hour at 15:50
– David Fort Myers
Nov 15 '18 at 18:18
add a comment |
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53323718%2fsql-server-and-allocate-time-into-hour-parts%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
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
answered Nov 15 '18 at 20:39
Dave CullumDave Cullum
6,16411329
Thank you Dave Collum. This works very well.
– David Fort Myers
Nov 19 '18 at 18:20
add a comment |
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
answered Nov 15 '18 at 20:39
Dave CullumDave Cullum
6,16411329
Thank you Dave Collum. This works very well.
– David Fort Myers
Nov 19 '18 at 18:20
add a comment |
1
1
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
answered Nov 15 '18 at 20:39
Dave CullumDave Cullum
6,16411329
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
answered Nov 15 '18 at 20:39
Dave CullumDave Cullum
6,16411329
edited Nov 15 '18 at 20:50
answered Nov 15 '18 at 20:39
Dave CullumDave Cullum
6,16411329
answered Nov 15 '18 at 20:39
Dave CullumDave Cullum
6,16411329
answered Nov 15 '18 at 20:39
Dave CullumDave Cullum
6,16411329
6,16411329
Thank you Dave Collum. This works very well.
– David Fort Myers
Nov 19 '18 at 18:20
add a comment |
Thank you Dave Collum. This works very well.
– David Fort Myers
Nov 19 '18 at 18:20
Thank you Dave Collum. This works very well.
– David Fort Myers
Nov 19 '18 at 18:20
Thank you Dave Collum. This works very well.
– David Fort Myers
Nov 19 '18 at 18:20
add a comment |
1
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
answered Nov 15 '18 at 16:41
sergeserge
69147
Hi Serge: This answer comes very close. However fire0002 does not include the span of the 12th hour which would be 60 minutes. I want to account for the time these fire trucks worked. Regarding fire0003 I needed to correct the EndDateTime (8th line of your query). It should have 60 - DATEDIFF(~~~~~~~) AS [MinutesForThisHourPart]. Your query returns 40 minutes remaining in the hour, but what I want to know is how many minutes this truck worked for that hour. Last: the fire0005 should return 30 minutes because it started at 16:20 and ended at 16:50.
– David Fort Myers
Nov 15 '18 at 18:30
add a comment |
1
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
answered Nov 15 '18 at 16:41
sergeserge
69147
Hi Serge: This answer comes very close. However fire0002 does not include the span of the 12th hour which would be 60 minutes. I want to account for the time these fire trucks worked. Regarding fire0003 I needed to correct the EndDateTime (8th line of your query). It should have 60 - DATEDIFF(~~~~~~~) AS [MinutesForThisHourPart]. Your query returns 40 minutes remaining in the hour, but what I want to know is how many minutes this truck worked for that hour. Last: the fire0005 should return 30 minutes because it started at 16:20 and ended at 16:50.
– David Fort Myers
Nov 15 '18 at 18:30
add a comment |
1
1
1
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
answered Nov 15 '18 at 16:41
sergeserge
69147
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
answered Nov 15 '18 at 16:41
sergeserge
69147
answered Nov 15 '18 at 16:41
sergeserge
69147
answered Nov 15 '18 at 16:41
sergeserge
69147
answered Nov 15 '18 at 16:41
sergeserge
69147
69147
Hi Serge: This answer comes very close. However fire0002 does not include the span of the 12th hour which would be 60 minutes. I want to account for the time these fire trucks worked. Regarding fire0003 I needed to correct the EndDateTime (8th line of your query). It should have 60 - DATEDIFF(~~~~~~~) AS [MinutesForThisHourPart]. Your query returns 40 minutes remaining in the hour, but what I want to know is how many minutes this truck worked for that hour. Last: the fire0005 should return 30 minutes because it started at 16:20 and ended at 16:50.
– David Fort Myers
Nov 15 '18 at 18:30
add a comment |
Hi Serge: This answer comes very close. However fire0002 does not include the span of the 12th hour which would be 60 minutes. I want to account for the time these fire trucks worked. Regarding fire0003 I needed to correct the EndDateTime (8th line of your query). It should have 60 - DATEDIFF(~~~~~~~) AS [MinutesForThisHourPart]. Your query returns 40 minutes remaining in the hour, but what I want to know is how many minutes this truck worked for that hour. Last: the fire0005 should return 30 minutes because it started at 16:20 and ended at 16:50.
– David Fort Myers
Nov 15 '18 at 18:30
Hi Serge: This answer comes very close. However fire0002 does not include the span of the 12th hour which would be 60 minutes. I want to account for the time these fire trucks worked. Regarding fire0003 I needed to correct the EndDateTime (8th line of your query). It should have 60 - DATEDIFF(~~~~~~~) AS [MinutesForThisHourPart]. Your query returns 40 minutes remaining in the hour, but what I want to know is how many minutes this truck worked for that hour. Last: the fire0005 should return 30 minutes because it started at 16:20 and ended at 16:50.
– David Fort Myers
Nov 15 '18 at 18:30
Hi Serge: This answer comes very close. However fire0002 does not include the span of the 12th hour which would be 60 minutes. I want to account for the time these fire trucks worked. Regarding fire0003 I needed to correct the EndDateTime (8th line of your query). It should have 60 - DATEDIFF(~~~~~~~) AS [MinutesForThisHourPart]. Your query returns 40 minutes remaining in the hour, but what I want to know is how many minutes this truck worked for that hour. Last: the fire0005 should return 30 minutes because it started at 16:20 and ended at 16:50.
– David Fort Myers
Nov 15 '18 at 18:30
add a comment |
1
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
answered Nov 15 '18 at 16:52
GuidoGGuidoG
5,52031844
Hi Guido: This answer comes very close. First issue is that fire0002 also spans the 12 hour and would put 60 minutes there. Also for fire0002 it ends at 13:10, therefore it would put 10 minutes in the 13th hour whereas you have 50. I modified the fifth line in your query to read "60 - ........ AS [EndMinutes] to correct this issue. A second issue would be fire0005. In this event there are 30 minutes within the 16th hour. This event starts at 16:20 and ends in the same hour at 15:50
– David Fort Myers
Nov 15 '18 at 18:18
add a comment |
1
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
answered Nov 15 '18 at 16:52
GuidoGGuidoG
5,52031844
Hi Guido: This answer comes very close. First issue is that fire0002 also spans the 12 hour and would put 60 minutes there. Also for fire0002 it ends at 13:10, therefore it would put 10 minutes in the 13th hour whereas you have 50. I modified the fifth line in your query to read "60 - ........ AS [EndMinutes] to correct this issue. A second issue would be fire0005. In this event there are 30 minutes within the 16th hour. This event starts at 16:20 and ends in the same hour at 15:50
– David Fort Myers
Nov 15 '18 at 18:18
add a comment |
1
1
1
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
answered Nov 15 '18 at 16:52
GuidoGGuidoG
5,52031844
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
answered Nov 15 '18 at 16:52
GuidoGGuidoG
5,52031844
answered Nov 15 '18 at 16:52
GuidoGGuidoG
5,52031844
answered Nov 15 '18 at 16:52
GuidoGGuidoG
5,52031844
answered Nov 15 '18 at 16:52
GuidoGGuidoG
5,52031844
5,52031844
Hi Guido: This answer comes very close. First issue is that fire0002 also spans the 12 hour and would put 60 minutes there. Also for fire0002 it ends at 13:10, therefore it would put 10 minutes in the 13th hour whereas you have 50. I modified the fifth line in your query to read "60 - ........ AS [EndMinutes] to correct this issue. A second issue would be fire0005. In this event there are 30 minutes within the 16th hour. This event starts at 16:20 and ends in the same hour at 15:50
– David Fort Myers
Nov 15 '18 at 18:18
add a comment |
Hi Guido: This answer comes very close. First issue is that fire0002 also spans the 12 hour and would put 60 minutes there. Also for fire0002 it ends at 13:10, therefore it would put 10 minutes in the 13th hour whereas you have 50. I modified the fifth line in your query to read "60 - ........ AS [EndMinutes] to correct this issue. A second issue would be fire0005. In this event there are 30 minutes within the 16th hour. This event starts at 16:20 and ends in the same hour at 15:50
– David Fort Myers
Nov 15 '18 at 18:18
Hi Guido: This answer comes very close. First issue is that fire0002 also spans the 12 hour and would put 60 minutes there. Also for fire0002 it ends at 13:10, therefore it would put 10 minutes in the 13th hour whereas you have 50. I modified the fifth line in your query to read "60 - ........ AS [EndMinutes] to correct this issue. A second issue would be fire0005. In this event there are 30 minutes within the 16th hour. This event starts at 16:20 and ends in the same hour at 15:50
– David Fort Myers
Nov 15 '18 at 18:18
Hi Guido: This answer comes very close. First issue is that fire0002 also spans the 12 hour and would put 60 minutes there. Also for fire0002 it ends at 13:10, therefore it would put 10 minutes in the 13th hour whereas you have 50. I modified the fifth line in your query to read "60 - ........ AS [EndMinutes] to correct this issue. A second issue would be fire0005. In this event there are 30 minutes within the 16th hour. This event starts at 16:20 and ends in the same hour at 15:50
– David Fort Myers
Nov 15 '18 at 18:18
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53323718%2fsql-server-and-allocate-time-into-hour-parts%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
2
Sorry, but I cringe when I see your eventID column. Your eventID column is a horrible design. You should separate them into eventID and eventType. Also, please don't store "fire" as an event type on every row, use "F" and then FK to a new table that has eventType and eventDescription.
– KM.
Nov 15 '18 at 16:47