SQL Server中如何计算员工登录登出时间及班次划分？

嘿，这个多班次考勤统计的问题我之前帮不少人处理过，咱们一步步拆解来搞定它～

首先咱们得先对齐数据模型哈，假设你的考勤表大概是这个结构（如果实际表字段不一样，按需调整就行）：

CREATE TABLE EmployeeAttendance (
    EmployeeID INT,
    OperationTime DATETIME,
    OperationType VARCHAR(10) -- 'login' 表示登录，'logout' 表示登出
);

步骤1：给每一次操作标记所属班次 #

核心逻辑是：同一员工单日里，两次连续操作间隔超过5小时，就视为新班次。咱们用窗口函数LAG()来获取上一次操作的时间和类型，然后生成唯一的班次ID：

WITH AttendanceWithPrev AS (
    SELECT 
        EmployeeID,
        OperationTime,
        OperationType,
        -- 拿到上一次操作的时间，按员工+日期分组排序
        LAG(OperationTime) OVER (
            PARTITION BY EmployeeID, CAST(OperationTime AS DATE) 
            ORDER BY OperationTime
        ) AS PrevOperationTime,
        -- 拿到上一次操作的类型
        LAG(OperationType) OVER (
            PARTITION BY EmployeeID, CAST(OperationTime AS DATE) 
            ORDER BY OperationTime
        ) AS PrevOperationType
    FROM EmployeeAttendance
),
AttendanceWithShift AS (
    SELECT 
        *,
        -- 生成班次ID：第一次操作/间隔超5小时，都算新班次
        SUM(CASE 
            WHEN PrevOperationTime IS NULL THEN 1
            WHEN DATEDIFF(HOUR, PrevOperationTime, OperationTime) > 5 THEN 1
            ELSE 0
        END) OVER (
            PARTITION BY EmployeeID, CAST(OperationTime AS DATE) 
            ORDER BY OperationTime ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS ShiftID
    FROM AttendanceWithPrev
)
SELECT * FROM AttendanceWithShift;

步骤2：配对登录登出，计算单班次在岗时长 #

接下来要把每个班次的login和logout对应起来（这里默认每个班次都是login开头、logout结尾，如果有异常数据比如只有login没logout，后面可以补规则处理）：

WITH AttendanceWithPrev AS (
    SELECT 
        EmployeeID,
        OperationTime,
        OperationType,
        LAG(OperationTime) OVER (
            PARTITION BY EmployeeID, CAST(OperationTime AS DATE) 
            ORDER BY OperationTime
        ) AS PrevOperationTime,
        LAG(OperationType) OVER (
            PARTITION BY EmployeeID, CAST(OperationTime AS DATE) 
            ORDER BY OperationTime
        ) AS PrevOperationType
    FROM EmployeeAttendance
),
AttendanceWithShift AS (
    SELECT 
        *,
        SUM(CASE 
            WHEN PrevOperationTime IS NULL THEN 1
            WHEN DATEDIFF(HOUR, PrevOperationTime, OperationTime) > 5 THEN 1
            ELSE 0
        END) OVER (
            PARTITION BY EmployeeID, CAST(OperationTime AS DATE) 
            ORDER BY OperationTime ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS ShiftID
    FROM AttendanceWithPrev
),
ShiftDurations AS (
    SELECT 
        EmployeeID,
        CAST(OperationTime AS DATE) AS AttendanceDate,
        ShiftID,
        -- 提取当前班次的登录时间
        MAX(CASE WHEN OperationType = 'login' THEN OperationTime END) AS LoginTime,
        -- 提取当前班次的登出时间
        MAX(CASE WHEN OperationType = 'logout' THEN OperationTime END) AS LogoutTime,
        -- 计算单班次在岗时长（这里用分钟，你可以改成小时/秒）
        DATEDIFF(MINUTE, 
            MAX(CASE WHEN OperationType = 'login' THEN OperationTime END),
            MAX(CASE WHEN OperationType = 'logout' THEN OperationTime END)
        ) AS OnFloorMinutes
    FROM AttendanceWithShift
    GROUP BY EmployeeID, CAST(OperationTime AS DATE), ShiftID
)
SELECT * FROM ShiftDurations;

步骤3：统计总在岗&离岗时长 #

离岗时长要涵盖三个部分：当天0点到第一次登录的时间、班次之间的间隔时间、最后一次登出到当天23:59:59的时间。咱们继续扩展CTE来计算：

WITH AttendanceWithPrev AS (
    SELECT 
        EmployeeID,
        OperationTime,
        OperationType,
        LAG(OperationTime) OVER (
            PARTITION BY EmployeeID, CAST(OperationTime AS DATE) 
            ORDER BY OperationTime
        ) AS PrevOperationTime,
        LAG(OperationType) OVER (
            PARTITION BY EmployeeID, CAST(OperationTime AS DATE) 
            ORDER BY OperationTime
        ) AS PrevOperationType
    FROM EmployeeAttendance
),
AttendanceWithShift AS (
    SELECT 
        *,
        SUM(CASE 
            WHEN PrevOperationTime IS NULL THEN 1
            WHEN DATEDIFF(HOUR, PrevOperationTime, OperationTime) > 5 THEN 1
            ELSE 0
        END) OVER (
            PARTITION BY EmployeeID, CAST(OperationTime AS DATE) 
            ORDER BY OperationTime ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS ShiftID
    FROM AttendanceWithPrev
),
ShiftDurations AS (
    SELECT 
        EmployeeID,
        CAST(OperationTime AS DATE) AS AttendanceDate,
        ShiftID,
        MAX(CASE WHEN OperationType = 'login' THEN OperationTime END) AS LoginTime,
        MAX(CASE WHEN OperationType = 'logout' THEN OperationTime END) AS LogoutTime,
        DATEDIFF(MINUTE, 
            MAX(CASE WHEN OperationType = 'login' THEN OperationTime END),
            MAX(CASE WHEN OperationType = 'logout' THEN OperationTime END)
        ) AS OnFloorMinutes
    FROM AttendanceWithShift
    GROUP BY EmployeeID, CAST(OperationTime AS DATE), ShiftID
),
ShiftWithPrevLogout AS (
    SELECT 
        *,
        LAG(LogoutTime) OVER (
            PARTITION BY EmployeeID, AttendanceDate 
            ORDER BY ShiftID
        ) AS PrevShiftLogoutTime
    FROM ShiftDurations
),
DailyDurations AS (
    SELECT 
        EmployeeID,
        AttendanceDate,
        SUM(OnFloorMinutes) AS TotalOnFloorMinutes,
        -- 计算总离岗时长
        (
            -- 当天开始到第一次登录的时间
            DATEDIFF(MINUTE, CAST(AttendanceDate AS DATETIME), MIN(LoginTime))
            -- 班次之间的间隔时间
            + SUM(CASE 
                WHEN PrevShiftLogoutTime IS NOT NULL THEN DATEDIFF(MINUTE, PrevShiftLogoutTime, LoginTime)
                ELSE 0
            END)
            -- 最后一次登出到当天结束的时间
            + DATEDIFF(MINUTE, MAX(LogoutTime), DATEADD(SECOND, -1, DATEADD(DAY, 1, CAST(AttendanceDate AS DATETIME))))
        ) AS TotalOffFloorMinutes
    FROM ShiftWithPrevLogout
    GROUP BY EmployeeID, AttendanceDate
)
SELECT 
    EmployeeID,
    AttendanceDate,
    TotalOnFloorMinutes AS [总在岗时长(分钟)],
    CONVERT(VARCHAR(10), TotalOnFloorMinutes / 60) + '小时' + CONVERT(VARCHAR(10), TotalOnFloorMinutes % 60) + '分钟' AS [总在岗时长(格式化)],
    TotalOffFloorMinutes AS [总离岗时长(分钟)],
    CONVERT(VARCHAR(10), TotalOffFloorMinutes / 60) + '小时' + CONVERT(VARCHAR(10), TotalOffFloorMinutes % 60) + '分钟' AS [总离岗时长(格式化)]
FROM DailyDurations;

一些额外提示 #

• 如果遇到异常数据（比如只有login没logout、或者只有logout没login），可以根据业务规则补处理：比如未登出的记录用当天结束时间补登出，无效的孤立操作直接过滤。
• 时间单位可以按需调整，比如把分钟改成小时、秒都可以。
• 要是需要按周/月统计，只需要修改分组维度就行。

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