SQL Server并将时间分配到小时部分

Question

SQL Server并将时间分配到小时部分

sqlsql-servertemporal

6

背景：

救护车和消防车在紧急情况发生时有派遣时间，并且在紧急情况结束时有明确的结束时间。

例如：一个紧急情况（EventID = fire0001）在10:45:00发生，并在11:30:00结束。

另一个紧急情况（EventID = fire0002）在11:50:00开始，并在13:10:00结束。

问题：

我想解析从开始到结束的时间量，并将其放置在发生时间的小时部分。例如；fire0001在10:45开始，11:30结束。

我希望结果显示10小时部分有15分钟，11小时部分有30分钟。

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

这个信息对救护车的规划很有用，可以确定每天每个小时的利用率。

给定开始时间和结束时间，如何计算每小时花费的时间？

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;

- David Fort Myers

2

抱歉，但是当我看到你的eventID列时，我感到非常不舒服。你的eventID列设计得很糟糕。你应该将它们分成eventID和eventType。此外，请不要在每一行中存储“fire”作为事件类型，使用“F”，然后FK到一个新表，其中包含eventType和eventDescription。 - KM.

3个回答

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

- GuidoG

嗨，Guido：这个答案非常接近。第一个问题是fire0002也跨越了12小时，并且会在那里放置60分钟。此外，对于fire0002，它在13:10结束，因此它会在第13个小时放置10分钟，而你有50分钟。我修改了查询中的第五行，将其改为“60-........ AS [EndMinutes]”以解决此问题。第二个问题是fire0005。在这个事件中，有30分钟在第16个小时内。这个事件从16:20开始，在同一个小时内在15:50结束。 - David Fort Myers

1

我相信这实现了你想要的内容：

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

输出：

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 * 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

输出：

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

- Dave C

这个很好用。但是，我现在有一个跨越多天的数据集。火灾事件从2018年11月1日23:30:00开始，并延续到第二天，于2018年11月2日00:30:00结束。这将返回23小时部分的30分钟和0小时部分的30分钟。另一个火灾事件从2018年11月1日23:45:00开始，于2018年11月2日01:30:00结束。这将返回23小时部分的15分钟，0小时部分的60分钟和1小时部分的30分钟。现有代码不跨越多天。这可以做到吗？ - David Fort Myers

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- serge · Accepted Answer

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

结果

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