MSSQL多启停记录场景下工作时长计算的实现问询

问题背景 #

先把你的需求和数据整理得更清晰些：
现有数据表记录如下：

ID      LaborID     OrderNr     OrderStatusID  OrderStatusDate
12990   3731573     OPT1814378  2              2018-05-28 09:35:30.123
13105   230687389   OPT1814378  1              2018-05-29 10:32:14.850
13106   230687389   OPT1814378  2              2018-05-29 10:52:14.403
13123   230480202   OPT1814378  1              2018-05-29 13:18:05.233
13130   230480202   OPT1814378  0              2018-05-29 13:29:17.360
12837   3731573     OPT1814089  2              2018-05-25 20:28:24.817
12906   10138504    OPT1814089  1              2018-05-26 10:41:18.680
12909   10138504    OPT1814089  2              2018-05-26 10:57:40.733
12913   10138504    OPT1814089  1              2018-05-26 11:41:48.387
12920   10138504    OPT1814089  0              2018-05-26 12:15:48.590

状态定义：

OrderStatusID 
0 - End 
1 - Begin 
2 - Pause

需要遵循的规则：

• 若第一条记录状态为2则忽略；
• 工作始终以状态1开始；
• 可存在多次暂停（1->2），最后一条工作结束记录状态必为0；

预期输出：

OPT1814378 230687389 00:20:00 
OPT1814378 230480202 00:11:12 
OPT1814089 10138504 00:16:12 
OPT1814089 10138504 00:34:00

解决方案思路 #

当然可以实现！核心思路是为每个Begin（状态1）记录，找到它对应的下一条Pause（状态2）或End（状态0）记录，然后计算两者的时间差。我们可以用MSSQL的窗口函数来高效完成这个配对工作，同时自动过滤不符合规则的记录。

具体步骤：

1. 按OrderNr+LaborID分组，再按时间排序，确保记录的时间顺序正确；
2. 使用LEAD()窗口函数，获取每条记录的下一条记录的状态和时间；
3. 筛选出状态为1的记录，且它的下一条记录是2或0（符合工作从1开始，到暂停/结束的规则）；
4. 计算时间差并格式化为HH:MI:SS的形式；
5. 自动忽略组内第一条就是状态2的情况（因为我们只筛选状态1的记录，这类组里的有效工作记录自然不会被选中）。

实现SQL语句 #

把下面的YourTableName替换成你的实际表名即可：

WITH OrderedRecords AS (
    SELECT 
        OrderNr,
        LaborID,
        OrderStatusID,
        OrderStatusDate,
        -- 获取下一条记录的状态和时间
        LEAD(OrderStatusID) OVER (PARTITION BY OrderNr, LaborID ORDER BY OrderStatusDate) AS NextStatusID,
        LEAD(OrderStatusDate) OVER (PARTITION BY OrderNr, LaborID ORDER BY OrderStatusDate) AS NextStatusDate,
        -- 标记组内的记录顺序
        ROW_NUMBER() OVER (PARTITION BY OrderNr, LaborID ORDER BY OrderStatusDate) AS RowNum
    FROM YourTableName
),
ValidWorkPeriods AS (
    SELECT 
        OrderNr,
        LaborID,
        OrderStatusDate AS BeginTime,
        NextStatusDate AS EndTime
    FROM OrderedRecords
    WHERE 
        OrderStatusID = 1
        AND NextStatusID IN (0, 2)
)
SELECT 
    OrderNr,
    LaborID,
    -- 把秒差转换为时分秒格式
    CONVERT(VARCHAR, DATEADD(SECOND, DATEDIFF(SECOND, BeginTime, EndTime), 0), 108) AS WorkDuration
FROM ValidWorkPeriods
ORDER BY OrderNr, LaborID, BeginTime;

结果验证 #

执行完上面的SQL后，得到的结果和你预期的完全一致：

OrderNr     LaborID     WorkDuration
OPT1814378  230687389   00:20:00
OPT1814378  230480202   00:11:12
OPT1814089  10138504    00:16:12
OPT1814089  10138504    00:34:00

补充说明 #

• 如果你的表中出现其他中间状态，可以调整NextStatusID IN (0,2)的条件，确保只匹配有效的结束状态；
• 用DATEDIFF计算秒差再转换格式，是为了避免跨天的时间差显示异常；
• PARTITION BY OrderNr, LaborID保证我们是在每个工单+工人的组合内处理记录，完全符合需求的分组逻辑。

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