SQL Server下特定状态关联连续记录的时间差计算方法求助

实现思路 #

1. 先按StatusEntryID分组，对组内的状态按statusDate升序排序，用LAG窗口函数取上一条状态的时间，计算两条记录的时间差，组内第一条记录无前置状态，时间差默认记为0。
2. 通过PIVOT行转列，把不同状态对应的时间差转为单独列，不存在的状态默认返回0。

完整代码示例 #

-- 测试表结构与样例数据（实际使用时替换为你自己的表）
DECLARE @statusTable TABLE (
    pkey INT,
    statusDate DATETIME,
    Status VARCHAR(20),
    StatusEntryID INT
)
INSERT INTO @statusTable VALUES
(1,'2021-10-07 11:49:59.430','Accept',7),
(2,'2021-10-07 11:51:50.430','Processed',7),
(3,'2021-10-07 11:52:52.883','Dispatched',7),
(4,'2021-10-07 11:55:37.263','Delivered',7)

-- 核心查询逻辑
;WITH status_time_diff AS (
    SELECT 
        StatusEntryID AS EntryId,
        Status,
        -- 此处计算的是分钟差，可替换DATEDIFF第一个参数调整时间单位：SECOND秒、HOUR小时等
        ISNULL(DATEDIFF(MINUTE, LAG(statusDate,1) OVER (PARTITION BY StatusEntryID ORDER BY statusDate), statusDate), 0) AS time_diff
    FROM @statusTable
)
SELECT 
    EntryId,
    ISNULL([Approve], 0) AS Time_Approve,
    ISNULL([Processed], 0) AS Time_Process,
    ISNULL([Dispatched], 0) AS Time_Dispatch,
    ISNULL([Delivered], 0) AS Time_Delivered
FROM status_time_diff
PIVOT (
    MAX(time_diff)
    FOR Status IN ([Approve], [Processed], [Dispatched], [Delivered])
) AS pivot_result

说明 #

• 如果某个状态（如示例中的Approve）不存在于物理表中，对应列会自动返回0，无需额外处理
• 支持状态跳过的场景，比如直接从Accept跳转至Delivered，中间缺失的状态列也会返回0，不影响其他状态的时间差计算
• 可根据需求修改DATEDIFF的第一个参数调整时间差的统计单位，也可以修改ISNULL的默认返回值为NULL，适配不同业务要求

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