如何在SQL中计算所有员工的工作时间重叠时段

现有一份员工工作时段列表，每位员工的工作时段各不相同，需要筛选出所有员工同时在岗的时段，剔除仅单个或部分员工在岗的时段。

测试数据 #

尝试的SQL代码（未得到预期结果） #

DECLARE @tmpSchedules TABLE (
    sDate DATETIME
    ,startTime DATETIME
    ,endTime DATETIME
    ,name varchar(255)
)

INSERT INTO @tmpSchedules
SELECT '20230223', '20230223 08:00', '20230223 10:00', 'John'
UNION ALL
SELECT '20230223', '20230223 10:30', '20230223 12:00', 'John'
UNION ALL
SELECT '20230223', '20230223 13:00', '20230223 17:00', 'John'
UNION ALL
SELECT '20230223', '20230223 08:30', '20230223 09:00', 'Anita'
UNION ALL
SELECT '20230223', '20230223 09:30', '20230223 20:00', 'Anita'

select * from @tmpSchedules

SELECT CA.sDate, CA.startTime, CA.endTime
,CASE
WHEN LAG(CA.endTime) OVER (ORDER BY CA.starttime) > CA.startTime THEN 0
ELSE 1
END OVL
FROM @tmpSchedules CA

预期输出 #

工作时段重叠可视化 #

解决方案 #

原代码使用LAG函数时，仅对所有记录按开始时间排序后判断前后时段是否重叠，未区分员工，也无法统计时段内的在岗员工数量，因此无法筛选出全员在岗的区间。

正确思路：

1. 提取所有时间节点（员工的开始、结束时间），生成连续无间隙的时间区间；
2. 统计每个区间内的在岗员工数量；
3. 筛选出员工数量等于总员工数的区间，计算时长。

实现代码：

DECLARE @tmpSchedules TABLE (
    sDate DATETIME
    ,startTime DATETIME
    ,endTime DATETIME
    ,name varchar(255)
)

INSERT INTO @tmpSchedules
SELECT '20230223', '20230223 08:00', '20230223 10:00', 'John'
UNION ALL
SELECT '20230223', '20230223 10:30', '20230223 12:00', 'John'
UNION ALL
SELECT '20230223', '20230223 13:00', '20230223 17:00', 'John'
UNION ALL
SELECT '20230223', '20230223 08:30', '20230223 09:00', 'Anita'
UNION ALL
SELECT '20230223', '20230223 09:30', '20230223 20:00', 'Anita'

-- 获取总员工数
DECLARE @totalEmployees INT = (SELECT COUNT(DISTINCT name) FROM @tmpSchedules)

-- 生成所有时间节点并排序
;WITH TimePoints AS (
    SELECT startTime AS pointTime FROM @tmpSchedules
    UNION
    SELECT endTime AS pointTime FROM @tmpSchedules
),
-- 生成连续的时间区间
TimeIntervals AS (
    SELECT 
        tp1.pointTime AS intervalStart,
        tp2.pointTime AS intervalEnd
    FROM TimePoints tp1
    JOIN TimePoints tp2 ON tp1.pointTime < tp2.pointTime
    -- 确保区间无间隙
    WHERE NOT EXISTS (
        SELECT 1 FROM TimePoints tp3 
        WHERE tp3.pointTime > tp1.pointTime AND tp3.pointTime < tp2.pointTime
    )
)
-- 筛选全员在岗的区间并计算时长
SELECT 
    ts.sDate,
    ti.intervalStart AS startTime,
    ti.intervalEnd AS endTime,
    DATEDIFF(MINUTE, ti.intervalStart, ti.intervalEnd) AS Duration
FROM TimeIntervals ti
CROSS JOIN (SELECT DISTINCT sDate FROM @tmpSchedules) ts
JOIN @tmpSchedules ts2 ON ti.intervalStart < ts2.endTime AND ti.intervalEnd > ts2.startTime
GROUP BY ts.sDate, ti.intervalStart, ti.intervalEnd
HAVING COUNT(DISTINCT ts2.name) = @totalEmployees
ORDER BY ti.intervalStart

代码说明 #

1. TimePoints CTE：收集所有员工的开始、结束时间，去重后得到关键时间节点；
2. TimeIntervals CTE：将时间节点配对，生成连续无间隙的时间区间；
3. 最后关联员工时段表，统计每个区间的在岗员工数，筛选出全员在岗的区间并计算时长。

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