我相信这实现了你想要的内容:
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 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
输出:
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
正如评论中所提到的,您存储EventID
的方式远非最佳。更好的方法是为每个事件分配一个“类型”,例如:
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 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
输出:
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