合并员工重叠/连续打卡班次 解决小时人员统计重复问题

问题核心 #

员工短时间内退卡再打卡（连续或间隔极短的班次）会导致原SQL统计每小时在岗人数时重复计数，需要先将这类重叠/连续的班次合并为单条记录，间隔较长的班次保留原样。

解决方案：先合并班次，再统计人数 #

步骤1：编写班次合并逻辑 #

使用窗口函数识别需要合并的班次组，这里设定间隔小于15分钟的班次需要合并（可根据实际需求调整阈值）：

WITH Merged_Shifts AS (
    -- 标记每个班次所属的合并组
    SELECT 
        Emp_Int,
        Start_Time,
        End_Time,
        -- 当前班次与上一班次间隔≤15分钟则归为同一组，否则生成新组
        SUM(CASE WHEN DATEDIFF(MINUTE, LAG(End_Time) OVER (PARTITION BY Emp_Int, CAST(Start_Time AS DATE) ORDER BY Start_Time), Start_Time) <= 15 THEN 0 ELSE 1 END) 
            OVER (PARTITION BY Emp_Int, CAST(Start_Time AS DATE) ORDER BY Start_Time) AS Shift_Group
    FROM TIME_SHIFTS_TABLE
),
-- 按组合并班次，取每组最早开始、最晚结束时间
Final_Merged_Shifts AS (
    SELECT 
        Emp_Int,
        MIN(Start_Time) AS Merged_Start_Time,
        MAX(End_Time) AS Merged_End_Time,
        CAST(MIN(Start_Time) AS DATE) AS Shift_Date
    FROM Merged_Shifts
    GROUP BY Emp_Int, Shift_Group, CAST(Start_Time AS DATE)
),
-- 生成24小时序列
Hours AS(
    SELECT 1 AS HOUR
    UNION ALL
    SELECT HOUR + 1 FROM Hours WHERE HOUR < 24
)
-- 基于合并后的班次统计每小时在岗人数
SELECT 
   F.Shift_Date AS [DATE], 
   H.HOUR, 
   COUNT(DISTINCT F.Emp_Int) AS [Head Count]
FROM 
   Hours H
LEFT JOIN 
   Final_Merged_Shifts F 
   ON H.HOUR BETWEEN DATEPART(HOUR, F.Merged_Start_Time) AND DATEPART(HOUR, F.Merged_End_Time)
GROUP BY 
   F.Shift_Date, H.HOUR
ORDER BY 
   F.Shift_Date, H.HOUR asc

关键说明 #

1. 阈值调整：代码中DATEDIFF(MINUTE, ...) <= 15的15是间隔分钟数阈值，可根据业务规则修改（比如改为5分钟）。
2. 分组逻辑：按员工ID+日期分组，确保仅合并同一天内的连续/短间隔班次。
3. 去重计数：使用COUNT(DISTINCT Emp_Int)进一步避免极端场景下的重复统计。
4. 兼容原有逻辑：保留了原有的小时序列生成逻辑，保证统计维度与原查询一致。

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