如何计算SQL中当前行InRoomTime与上一行OutRoomTime的DateDiff

需求：计算行间时间差，具体为前一行的OutRoomTime与当前行的InRoomTime的分钟差值，第1行无对应差值时显示指定文本。

表结构与测试数据 #

CREATE TABLE Surgery 
(
    AccountNumber varchar(20),
    CaseNumber varchar(6),
    SurgDate DATETIME,
    ORRoom varchar(10),
    Surgeon varchar(50),
    InRoomTime TIME(3),
    OutRoomTime TIME(3)
)

INSERT INTO Surgery 
VALUES
('11111', '144995', '2024-07-19 00:00:00.000', 'OR06', 'SOK', '07:29', '08:30'),
('11111', '145017', '2024-07-19 00:00:00.000', 'OR06', 'SOK', '09:00', '10:18'),
('11111', '145028', '2024-07-19 00:00:00.000', 'OR06', 'SOK', '11:03', '13:00'),
('11111', '145027', '2024-07-19 00:00:00.000', 'OR06', 'SOK', '13:42', '16:20')

尝试的SQL语句 #

--SELECT * FROM Surgery

SELECT 
    InRoomTime,  
    (SELECT OutRoomTime
     FROM SURGERY AS table2
     WHERE table2.SurgDate = SurgDate AND table2.OutRoomTime < InRoomTime 
     --ORDER BY OutRoomTime DESC
    ) AS TOT
FROM SURGERY

期望输出结果 #

11111  144995  2024-07-19 00:00:00.000 OR06    SOK 07:29:00.000    08:30:00.000 Time Difference mins
11111   145017  2024-07-19 00:00:00.000 OR06    SOK 09:00:00.000    10:18:00.000 30
11111   145028  2024-07-19 00:00:00.000 OR06    SOK 11:03:00.000    13:00:00.000 45
11111   145027  2024-07-19 00:00:00.000 OR06    SOK 13:42:00.000    16:20:00.000 158

解决方案 #

使用窗口函数LAG()精准获取上一行的OutRoomTime，结合DATEDIFF()计算分钟差值，同时确保按手术日期和手术室分组、按进入时间排序，保证行顺序正确：

SELECT 
    AccountNumber,
    CaseNumber,
    SurgDate,
    ORRoom,
    Surgeon,
    InRoomTime,
    OutRoomTime,
    CASE 
        WHEN LAG(OutRoomTime) OVER (PARTITION BY SurgDate, ORRoom ORDER BY InRoomTime) IS NOT NULL
        THEN CAST(DATEDIFF(MINUTE, LAG(OutRoomTime) OVER (PARTITION BY SurgDate, ORRoom ORDER BY InRoomTime), InRoomTime) AS VARCHAR(10))
        ELSE 'Time Difference mins'
    END AS [Time Difference mins]
FROM Surgery
ORDER BY InRoomTime;

核心逻辑说明 #

1. LAG()函数：按手术日期和手术室分组，按进入房间时间排序，获取当前行的上一行OutRoomTime，确保只取紧邻的前一次手术的离开时间。
2. DATEDIFF(MINUTE, ...)：计算上一行离开时间与当前行进入时间的分钟差值，转换为字符串保证与标题文本格式统一。
3. CASE判断：第一行无前置数据时显示指定标题文本，其余行显示计算出的分钟差。

内容的提问来源于stack exchange，提问作者David Carreon