SQL Server基于打卡时间戳计算员工考勤（含跨夜班次）

嘿，我来帮你搞定这个SQL Server的考勤统计问题！先理清楚咱们的需求和手头的数据：

首先是咱们的timeclock表，里面存的是员工的打卡时间戳：

Userid CheckTime
312    2018-05-08 05:52:00
312    2018-05-08 18:06:00
312    2018-05-10 05:55:00
312    2018-05-10 18:00:00
312    2018-05-11 05:58:00
312    2018-05-11 18:00:00
312    2018-05-12 05:35:00
312    2018-05-12 18:00:00

咱们要把这些数据转换成清晰的考勤报表，格式如下：

Day        Date      In      Out     Reg   OT
Tuesday    5/8/2018  5:52AM  6:06PM  12.00 0.00
Thursday   5/10/2018 5:55AM  6:00PM  12.00 0.00
Friday     5/11/2018 5:58AM  6:00PM  12.00 0.00
Saturday   5/12/2018 5:35AM  6:00PM  12.00 0.42

我看你提到了两个关键点：一是打卡记录是行式存储，没法直接配对上下班；二是还有跨天班次的员工，之前尝试的CTE写法没搞定这些场景，结果不对。

下面给你一个能处理跨天情况的SQL脚本，思路是先给每个用户的打卡记录按时间排序，自动配对上下班，同时处理跨天的日期归属：

WITH ClockPairs AS (
    SELECT 
        Userid,
        CheckTime AS InTime,
        -- 用LEAD获取下一条打卡记录作为下班时间
        LEAD(CheckTime) OVER (PARTITION BY Userid ORDER BY CheckTime) AS OutTime,
        -- 标记当前记录是否是上班打卡（奇数行）
        ROW_NUMBER() OVER (PARTITION BY Userid ORDER BY CheckTime) % 2 AS IsInRecord,
        -- 处理跨天：如果下班在第二天，考勤日期归到上班当天
        CAST(CASE 
            WHEN DATEDIFF(DAY, CheckTime, LEAD(CheckTime) OVER (PARTITION BY Userid ORDER BY CheckTime)) > 0 
            THEN CheckTime 
            ELSE LEAD(CheckTime) OVER (PARTITION BY Userid ORDER BY CheckTime) 
        END AS DATE) AS AttendanceDay
    FROM timeclock
    WHERE CheckTime BETWEEN '2018-05-06' AND '2018-05-13'
)
SELECT
    -- 把日期转成星期几的名称
    DATENAME(WEEKDAY, AttendanceDay) AS [Day],
    -- 格式化日期为MM/DD/YYYY格式
    FORMAT(AttendanceDay, 'M/d/yyyy') AS [Date],
    -- 把上班时间转成12小时制带AM/PM
    FORMAT(InTime, 'h:mmtt') AS [In],
    -- 把下班时间转成12小时制带AM/PM
    FORMAT(OutTime, 'h:mmtt') AS [Out],
    -- 正常工时这里假设是固定12小时，要是有班次表可以关联替换
    CAST(12.00 AS DECIMAL(5,2)) AS [Reg],
    -- 计算加班：实际工时减正常班，不足0就按0算
    CAST(MAX(0.0, DATEDIFF(MINUTE, InTime, OutTime)/60.0 - 12) AS DECIMAL(5,2)) AS [OT]
FROM ClockPairs
WHERE IsInRecord = 1 -- 只保留上班打卡的行，配对对应的下班记录
AND OutTime IS NOT NULL -- 过滤掉没有下班记录的无效打卡
GROUP BY Userid, AttendanceDay, InTime, OutTime
ORDER BY AttendanceDay;

脚本说明 #

1. ClockPairs CTE：用LEAD()函数自动把每一条打卡记录和下一条配对，完美解决行式存储的配对问题；通过CASE判断跨天情况，确保考勤日期归属正确。
2. 格式处理：用DATENAME和FORMAT把日期时间转换成报表需要的友好格式，不用额外做字符串拼接。
3. 加班计算：先把分钟转成小时，减去正常12小时，正数就是加班时长，避免出现负数加班。
4. 灵活调整：如果你的正常班次不是12小时，直接把脚本里的12.00替换成对应的时长就行，要是有专门的班次表，也可以关联进去动态获取。

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