如何基于时间间隔为SQL员工打卡记录生成班次分组列？

问题背景 #

SQL表中存储员工打卡记录，需按班次分组识别起止，存在两种场景：

• 当日班次：打卡记录均在同一天内
• 跨天班次：当日开始次日结束，当晚开启新班次

要求按照示例表中「*New Group」列的形式分组，计划通过Lag()函数计算上一条记录的EndLocal与当前记录StartLocal的间隔小时数（即「Lag() Hrs」），当该值大于10时生成新分组。此前尝试用ROW_NUMBER()实现，但无法在间隔小时数>10时正确生成新分组ID。

示例数据 #

尝试的代码 #

with cte_punches as (
        SELECT
            t.Id,
            e.EmployeeId,
            t.DateLocal,
            t.StartLocal,
            t.EndLocal,
            COALESCE(ABS(DATEDIFF(hh, LAG(t.EndLocal, 1,NULL) OVER(PARTITION BY e.EmployeeId Order BY t.Id),t.StartLocal)), 0) ClockedHours
        FROM
            dbo.prod_bamboo_timesheetentries AS t
            LEFT OUTER JOIN dbo.prod_bamboo_employeelist AS e ON t.EmployeeId = e.Id 
        WHERE
            (UPPER(e.Location) LIKE '%PH%'
            AND NOT UPPER(t.TaskName) LIKE '%BREAK%'
            AND NOT UPPER(t.TaskName) LIKE '%LUNCH%'
            OR t.TaskName IS NULL)
                and e.EmployeeId = '6917' --'5526'
                and t.DateLocal between '2025-01-01' and '2025-01-10'
        )
SELECT
    Id,
    EmployeeId,
    DateLocal,
    StartLocal,
    EndLocal,
    ClockedHours,
    row_number() OVER(PARTITION BY ClockedHours ORDER BY Id) GroupIdentifier
FROM
    cte_punches
ORDER BY 
    EmployeeId,
    Id

正确解决方案 #

核心思路是用累加窗口函数标记新分组：当ClockedHours>10时，视为新班次的开始，累加1；否则累加0。通过这个累加值作为分组ID，再根据分组内的最早日期生成「*New Group」格式的名称。

修改后的SQL代码如下：

WITH cte_punches AS (
    SELECT
        t.Id,
        e.EmployeeId,
        t.DateLocal,
        t.StartLocal,
        t.EndLocal,
        -- 计算与上一条记录的间隔小时数
        COALESCE(ABS(DATEDIFF(hh, LAG(t.EndLocal, 1, NULL) OVER(PARTITION BY e.EmployeeId ORDER BY t.Id), t.StartLocal)), 0) AS ClockedHours
    FROM
        dbo.prod_bamboo_timesheetentries AS t
        LEFT JOIN dbo.prod_bamboo_employeelist AS e ON t.EmployeeId = e.Id 
    WHERE
        (UPPER(e.Location) LIKE '%PH%'
         AND NOT UPPER(t.TaskName) LIKE '%BREAK%'
         AND NOT UPPER(t.TaskName) LIKE '%LUNCH%'
         OR t.TaskName IS NULL)
        AND e.EmployeeId = '6917'
        AND t.DateLocal BETWEEN '2025-01-01' AND '2025-01-10'
),
cte_groups AS (
    SELECT
        *,
        -- 生成分组ID：间隔>10时加1，累加得到分组标识
        SUM(CASE WHEN ClockedHours > 10 THEN 1 ELSE 0 END) OVER(PARTITION BY EmployeeId ORDER BY Id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS GroupId
    FROM cte_punches
)
SELECT
    Id,
    EmployeeId,
    DateLocal,
    StartLocal,
    EndLocal,
    ClockedHours,
    -- 根据分组内最早的StartLocal生成Shift名称
    CONCAT('Shift ', FORMAT(MIN(StartLocal) OVER(PARTITION BY EmployeeId, GroupId), 'MM/dd/yy')) AS [*New Group]
FROM cte_groups
ORDER BY EmployeeId, Id;

代码说明 #

1. cte_punches：保留原逻辑，计算每条记录与上一条的间隔小时数ClockedHours。
2. cte_groups：通过SUM(CASE...) OVER()窗口函数，对每个员工的记录按ID排序，当间隔小时数>10时累加1，生成唯一的GroupId，同一班次的记录会拥有相同的GroupId。
3. 最终查询：通过MIN(StartLocal) OVER(PARTITION BY EmployeeId, GroupId)获取每个班次的最早开始时间，格式化为「Shift MM/dd/yy」的形式，匹配示例中的分组名称。

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