SQL Server中基于条件的员工登录登出时长计算问题

嘿，针对你这个SQL Server统计员工多班次在岗离岗时长的问题，我来给你梳理下解决方案～首先得补个小前提：你给的表结构不完整，我假设你的emp表包含这些关键字段（如果实际字段不一样，你可以对应调整）：

• [Emp Id]：员工ID（虽然是float类型，但建议后续转成int或varchar避免精度坑）
• [Time]：打卡事件的具体时间（datetime类型）
• [Event]：事件类型，比如'Login'代表登录/在岗，'Logout'代表登出/离岗；要是你用[Area Of Access]来区分状态（比如进入办公区算在岗），后续替换判断条件就行

第一步：把登录和登出记录配对起来 #

首先得把每个员工的登录和对应的登出记录匹配上，这里用LEAD()窗口函数就能轻松拿到同员工下一条事件的时间和类型：

WITH EmpEvents AS (
    SELECT
        [Emp Id],
        CAST([Time] AS DATE) AS [EventDate], -- 提取日期，按天分组
        [Time] AS [CurrentTime],
        [Event] AS [CurrentEvent],
        -- 获取下一条事件的时间和类型
        LEAD([Time]) OVER (PARTITION BY [Emp Id], CAST([Time] AS DATE) ORDER BY [Time]) AS [NextTime],
        LEAD([Event]) OVER (PARTITION BY [Emp Id], CAST([Time] AS DATE) ORDER BY [Time]) AS [NextEvent]
    FROM [dbo].[emp]
),
-- 筛选出有效的登录-登出配对，排除无效的孤立记录
ValidShifts AS (
    SELECT
        [Emp Id],
        [EventDate],
        [CurrentTime] AS [ShiftStart],
        [NextTime] AS [ShiftEnd],
        DATEDIFF(MINUTE, [CurrentTime], [NextTime]) AS [ShiftDuration] -- 单班次时长（分钟）
    FROM EmpEvents
    WHERE [CurrentEvent] = 'Login' -- 只把登录作为班次起点
      AND [NextEvent] = 'Logout' -- 确保下一条是对应的登出
),
-- 计算班次之间的间隔，判断是否属于多班次
ShiftIntervals AS (
    SELECT
        *,
        -- 获取下一班次的开始时间
        LEAD([ShiftStart]) OVER (PARTITION BY [Emp Id], [EventDate] ORDER BY [ShiftStart]) AS [NextShiftStart],
        -- 计算两个班次之间的间隔（分钟），5小时就是300分钟
        DATEDIFF(MINUTE, [ShiftEnd], LEAD([ShiftStart]) OVER (PARTITION BY [Emp Id], [EventDate] ORDER BY [ShiftStart])) AS [IntervalMinutes]
    FROM ValidShifts
)

第二步：统计当日的在岗和离岗时长 #

接下来就可以按员工和日期聚合计算了。在岗时长就是所有有效班次的时长总和，离岗时长则是当天总分钟数（24*60=1440分钟）减去在岗时长，同时我们还能顺便统计当日的班次数量：

SELECT
    [Emp Id],
    [EventDate],
    -- 把总在岗时长转成小时:分钟的友好格式
    CONVERT(VARCHAR(5), DATEADD(MINUTE, SUM([ShiftDuration]), 0), 108) AS [OnFloorTime],
    -- 计算总离岗时长，同样转成小时:分钟格式
    CONVERT(VARCHAR(5), DATEADD(MINUTE, 1440 - SUM([ShiftDuration]), 0), 108) AS [OffFloorTime],
    -- 统计班次数量：间隔≥300分钟（5小时）就算新班次
    COUNT(CASE WHEN [IntervalMinutes] IS NULL OR [IntervalMinutes] >= 300 THEN 1 END) AS [ShiftCount]
FROM ShiftIntervals
GROUP BY [Emp Id], [EventDate]
ORDER BY [Emp Id], [EventDate];

几个特殊情况的处理建议 #

• 如果遇到员工当天只登录没登出的情况：可以在ValidShifts里把登出时间设为当日的23:59:59，或者单独标记为“未完成班次”，避免数据遗漏。
• 要是你不用[Event]字段，而是靠[Area Of Access]判断状态：只需要把WHERE里的[CurrentEvent] = 'Login'换成对应的条件（比如[Area Of Access] = 'Office'）就行。
• 关于[Emp Id]的float类型：建议用CAST([Emp Id] AS INT) AS [EmpId]转成整数，避免因为float精度问题导致分组错误。

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