基于自动班次分配的SQL考勤总工时计算方案求助

问题说明 #

考勤表包含TableId、EmpId、AttendanceDateTime字段，员工每次打卡新增一条记录，允许多次打卡。规则如下：

• 单次班次内的首条记录为上班打卡，末条为下班打卡
• 无固定员工-班次映射，需依据给定班次表自动分配班次

班次表数据 #

INSERT INTO #TempShifts (ShiftName, StartTime, EndTime, Hours, IsNightShift)
VALUES
('S1', '06:30', '15:30', 8, 0),
('S2', '14:30', '23:30', 8, 0),
('S3', '22:30', '07:30', 8, 1),
('S5', '06:30', '19:30', 12, 0),
('S4', '18:30', '07:30', 12, 1);

考勤测试数据 #

-- 插入临时表记录
INSERT INTO #TempAttendance (TableId,
                             EmpId,
                             AttendanceDateTime)
VALUES (1, 'A001', '2025-01-01 06:54:05'),
       (2, 'A001', '2025-01-01 11:58:23'),
       (3, 'A001', '2025-01-01 13:31:43'),
       (4, 'A001', '2025-01-01 15:00:11'),
       (5, 'A002', '2025-01-02 15:05:05'),
       (6, 'A002', '2025-01-02 17:54:05'),
       (7, 'A002', '2025-01-02 18:20:05'),
       (8, 'A002', '2025-01-02 22:55:05'),
       (9, 'A001', '2025-01-03 23:05:05'),
       (10, 'A001', '2025-01-04 02:20:05'),
       (11, 'A001', '2025-01-04 03:20:05'),
       (12, 'A001', '2025-01-04 07:10:05'),
       (13, 'A001', '2025-01-04 22:59:05'),
       (14, 'A001', '2025-01-05 03:20:05'),
       (15, 'A001', '2025-01-05 03:50:05'),
       (16, 'A001', '2025-01-05 07:00:05'),
       (17, 'A001', '2025-01-06 18:54:05'),
       (18, 'A001', '2025-01-06 18:58:23'),
       (19, 'A001', '2025-01-06 20:31:43'),
       (20, 'A001', '2025-01-06 20:50:11'),
       (21, 'A001', '2025-01-07 04:10:25'),
       (22, 'A001', '2025-01-07 04:25:54'),
       (23, 'A001', '2025-01-07 07:00:59'),
       (24, 'A001', '2025-01-07 07:06:36'),
       (25, 'A001', '2025-01-07 19:05:54'),
       (26, 'A001', '2025-01-07 19:01:33'),
       (27, 'A001', '2025-01-07 19:57:34'),
       (28, 'A001', '2025-01-07 20:26:27'),
       (29, 'A001', '2025-01-08 04:00:50'),
       (30, 'A001', '2025-01-08 07:02:23'),
       (31, 'A001', '2025-01-08 07:10:51'),
       (32, 'A001', '2025-01-10 07:00:59'),
       (33, 'A001', '2025-01-10 09:00:59'),
       (34, 'A001', '2025-01-10 09:40:59'),
       (35, 'A001', '2025-01-10 18:56:59'),
       (36, 'A001', '2025-01-11 06:58:54'),
       (37, 'A001', '2025-01-11 11:10:54'),
       (38, 'A001', '2025-01-11 12:02:54'),
       (39, 'A001', '2025-01-11 19:05:54');

预期输出 #

当前尝试的SQL及问题 #

我尝试了以下SQL查询，但夜班的上下班打卡记录匹配存在问题（跨天的班次无法正确关联上下班时间）：

SELECT 
    CAST(AttendanceDateTime AS DATE) AS [Date],
    EmpId,
    MIN(AttendanceDateTime) [IN-Time], 
    MAX(AttendanceDateTime) [Out-Time],
    RIGHT('0' + CAST(DATEDIFF(MINUTE,MIN(AttendanceDateTime), MAX(AttendanceDateTime)) /60 AS VARCHAR), 2) + ':' + RIGHT('0' + CAST(DATEDIFF(MINUTE,MIN(AttendanceDateTime), MAX(AttendanceDateTime)) %60 AS VARCHAR), 2) AS WorkingHrs
FROM   
    #TempAttendance
GROUP BY CAST(AttendanceDateTime AS DATE), EmpId

解决方案 #

要解决夜班跨天的匹配问题，核心是正确划分单个班次的时间周期，再根据上班打卡时间匹配对应班次。以下是完整的SQL实现：

WITH RankedAttendance AS (
    -- 给每个员工的打卡记录按时间排序，计算相邻打卡的时间间隔
    SELECT 
        TableId,
        EmpId,
        AttendanceDateTime,
        LAG(AttendanceDateTime) OVER (PARTITION BY EmpId ORDER BY AttendanceDateTime) AS PrevPunch,
        -- 标记是否为新班次的开始（间隔超过4小时视为新班次，可按需调整阈值）
        CASE 
            WHEN LAG(AttendanceDateTime) OVER (PARTITION BY EmpId ORDER BY AttendanceDateTime) IS NULL THEN 1
            WHEN DATEDIFF(HOUR, LAG(AttendanceDateTime) OVER (PARTITION BY EmpId ORDER BY AttendanceDateTime), AttendanceDateTime) > 4 THEN 1
            ELSE 0
        END AS IsNewShift
    FROM #TempAttendance
),
ShiftGroups AS (
    -- 给每个班次分配唯一分组ID
    SELECT 
        *,
        SUM(IsNewShift) OVER (PARTITION BY EmpId ORDER BY AttendanceDateTime) AS ShiftGroupId
    FROM RankedAttendance
),
ShiftSummary AS (
    -- 提取每个班次的首末打卡时间，确定班次归属日期（以上班打卡日期为准）
    SELECT 
        EmpId,
        ShiftGroupId,
        MIN(AttendanceDateTime) AS PunchInTime,
        MAX(AttendanceDateTime) AS PunchOutTime,
        CAST(MIN(AttendanceDateTime) AS DATE) AS ShiftDate
    FROM ShiftGroups
    GROUP BY EmpId, ShiftGroupId
),
ShiftMatching AS (
    -- 关联班次表，匹配最贴合的班次
    SELECT 
        ss.ShiftDate AS 日期,
        ss.EmpId AS 员工ID,
        ts.ShiftName AS 班次,
        FORMAT(ss.PunchInTime, 'HH:mm') AS 上班时间,
        FORMAT(ss.PunchOutTime, 'HH:mm') AS 下班时间,
        -- 计算上班时间与班次开始时间的分钟差，用于筛选最优匹配
        ABS(DATEDIFF(MINUTE, CAST(ts.StartTime AS TIME), CAST(ss.PunchInTime AS TIME))) AS MatchScore,
        ROW_NUMBER() OVER (PARTITION BY ss.EmpId, ss.ShiftGroupId ORDER BY ABS(DATEDIFF(MINUTE, CAST(ts.StartTime AS TIME), CAST(ss.PunchInTime AS TIME)))) AS RN
    FROM ShiftSummary ss
    LEFT JOIN #TempShifts ts ON 
        -- 白班匹配规则：上班时间在班次的开始与结束时间之间
        (ts.IsNightShift = 0 AND CAST(ss.PunchInTime AS TIME) BETWEEN ts.StartTime AND ts.EndTime)
        -- 夜班匹配规则：上班时间在班次开始到当天结束，或次日0点到班次结束
        OR (ts.IsNightShift = 1 AND (CAST(ss.PunchInTime AS TIME) >= ts.StartTime OR CAST(ss.PunchInTime AS TIME) <= ts.EndTime))
)
-- 筛选每个班次的最优匹配结果并排序
SELECT 
    日期,
    员工ID,
    班次,
    上班时间,
    下班时间
FROM ShiftMatching
WHERE RN = 1
ORDER BY 日期, 员工ID;

代码说明 #

1. RankedAttendance：为员工打卡记录排序，通过相邻打卡的时间间隔标记新班次的起始点，确保跨天班次被正确识别。
2. ShiftGroups：累加新班次标记，为每个独立班次分配唯一分组ID，实现跨天班次的精准分组。
3. ShiftSummary：提取每个班次的首末打卡时间，明确班次归属日期（以上班打卡日期为准，符合考勤统计习惯）。
4. ShiftMatching：根据白班/夜班的时间规则关联班次表，通过计算时间差筛选最贴合的班次，确保分配结果准确。

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