如何编写SQL按时间区间统计同时在岗的员工总人数

你要的在岗人数分时段统计可以通过事件拆分累计法实现，这是处理此类时间区间重叠统计的通用方案，适配SQL Server环境（你的原查询语法为SQL Server风格），完整实现代码如下：

WITH base_data AS (
    -- 基础数据：取出原始时间字段，处理跨天的离岗时间
    SELECT 
        linka.xLinka AS Line,
        linka.xDoklad AS Document,
        zam.xPracovnik AS Employee,
        -- 转成datetime类型方便计算，可根据实际字段类型调整转换逻辑
        CAST(zam.xCasOd AS DATETIME) AS arrival_time,
        CASE 
            WHEN CAST(zam.xCasDo AS DATETIME) < CAST(zam.xCasOd AS DATETIME) 
            THEN DATEADD(DAY, 1, CAST(zam.xCasDo AS DATETIME))
            ELSE CAST(zam.xCasDo AS DATETIME) 
        END AS departure_time
    FROM [K2CA_CA].[dbo].[_OV_Data01] as linka
    LEFT OUTER JOIN dbo._OV_Data03 as zam ON zam.xLinka = linka.xLinka and zam.xDoklad = linka.xDoklad
    WHERE linka.xRok = 2021
        --AND linka.xDen  >= '2021-10-20' and linka.xDen <= '2021-10-26'
        AND (zam.xPozice like '%Bale%' or zam.xPozice like '%Plnič%')
),
time_events AS (
    -- 拆分事件：到岗记+1，离岗记-1
    SELECT Line, Document, arrival_time AS event_time, 1 AS delta FROM base_data
    UNION ALL
    SELECT Line, Document, departure_time AS event_time, -1 AS delta FROM base_data
),
sorted_events AS (
    -- 计算累计在岗人数
    SELECT 
        Line,
        Document,
        event_time,
        SUM(delta) OVER (PARTITION BY Line, Document ORDER BY event_time) AS running_total
    FROM time_events
),
intervals AS (
    -- 拼接相邻时间点为统计区间
    SELECT 
        Line,
        Document,
        event_time AS interval_start,
        LEAD(event_time) OVER (PARTITION BY Line, Document ORDER BY event_time) AS interval_end,
        running_total AS on_duty_count
    FROM sorted_events
)
-- 输出最终结果，格式化时间和说明
SELECT 
    Line,
    Document,
    on_duty_count AS [COUNT(*)],
    CONCAT(
        FORMAT(interval_start, 'HH:mm'), 
        ' ', 
        FORMAT(interval_end, 'HH:mm'),
        ' (',
        FORMAT(interval_start, 'HH:mm'),
        ' - ',
        FORMAT(interval_end, 'HH:mm'),
        ' 共有 ',
        on_duty_count,
        ' 名员工在岗)'
    ) AS [Arrival   Departure]
FROM intervals
WHERE on_duty_count > 0 -- 过滤掉无人在岗的区间
ORDER BY Line, Document, interval_start

逻辑说明 #

• 第一步先处理原始时间字段，自动识别跨天的离岗时间（比如当日22点到次日6点的班次），避免时间计算错误
• 把每个员工的到岗、离岗拆成两个独立事件，到岗时在岗人数+1，离岗时-1
• 按时间排序所有事件，计算每个时间点的累计在岗人数
• 把相邻两个事件的时间点拼成统计区间，相同区间内的在岗人数完全一致
• 最后过滤掉无人在岗的区间，格式化输出即可

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