SQL Server 2014：计算REST VIOLATION间的RECALL总时长求助

样本数据表 #

实现SQL查询 #

假设数据表名为time_punches，以下查询可实现需求：

WITH rest_records AS (
    SELECT 
        PERSONNUM,
        PunchStart,
        PunchEnd,
        PayCodeName,
        -- 获取当前员工上一条REST记录的开始时间
        LAG(PunchStart) OVER (PARTITION BY PERSONNUM ORDER BY PunchStart) AS prev_rest_start
    FROM time_punches
    WHERE PayCodeName = 'REST VIOLATION'
),
recall_durations AS (
    SELECT 
        PERSONNUM,
        PunchStart AS recall_start,
        PunchEnd AS recall_end,
        -- 计算单条RECALL的时长（单位：小时）
        DATEDIFF(MINUTE, PunchStart, PunchEnd)/60.0 AS duration_hours
    FROM time_punches
    WHERE PayCodeName = 'RECALL'
)
SELECT 
    r.PERSONNUM,
    r.PunchStart,
    r.PunchEnd,
    r.PayCodeName,
    -- 无匹配RECALL时返回0
    COALESCE(SUM(rd.duration_hours), 0) AS `Recall in between`
FROM rest_records r
LEFT JOIN recall_durations rd
    ON r.PERSONNUM = rd.PERSONNUM
    -- 筛选出在上一条REST和当前REST之间的RECALL记录
    AND rd.recall_start > r.prev_rest_start
    AND rd.recall_end < r.PunchStart
GROUP BY r.PERSONNUM, r.PunchStart, r.PunchEnd, r.PayCodeName
ORDER BY r.PERSONNUM, r.PunchStart;

查询逻辑说明 #

1. rest_records CTE：筛选所有REST VIOLATION记录，通过LAG窗口函数为每条记录关联同一员工的上一条REST记录的开始时间，确定时间范围边界。
2. recall_durations CTE：筛选所有RECALL记录，计算每条记录的时长（转换为小时）。
3. 主查询：将两个CTE关联，匹配同一员工、且时间落在当前REST与上一条REST之间的RECALL记录，求和时长；无匹配时用COALESCE返回0。

最终输出结果 #

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