SQL Server合并重叠工时并生成功能状态的查询方案求助
2026-6-13
考勤时间段功能状态统计SQL解决方案求助
考勤表结构与数据
我在Microsoft SQL Server中有一张扁平化处理后的员工考勤记录表tctable，结构及示例数据如下：
| key | 员工ID | 员工姓名 | 岗位 | 开始时间 | 结束时间 |
|---|---|---|---|---|---|
| 1 | 1 | Bob | Manager | 2025-03-20 7:00 | 2025-03-20 11:00 |
| 2 | 1 | Bob | Manager | 2025-03-20 13:00 | 2025-03-20 17:00 |
| 3 | 2 | Frank | Manager | 2025-03-20 11:00 | 2025-03-20 12:00 |
| 4 | 2 | Frank | Manager | 2025-03-20 13:00 | 2025-03-20 19:15 |
| 5 | 3 | Anne | Cashier | 2025-03-20 8:00 | 2025-03-20 13:00 |
| 6 | 4 | Tim | Cashier | 2025-03-20 12:00 | 2025-03-20 17:00 |
| 7 | 5 | Carl | Warehouse | 2025-03-20 14:00 | null |
| 8 | 6 | Clara | Cashier | 2025-03-20 20:00 | null |
注：
null表示员工未打卡下班，默认当前时间为其结束时间。
功能状态定义
- Partial Functional（PF，部分可用）：有1名Manager加至少1名其他员工（含其他Manager），或同时有Cashier和Warehouse；
- Fully Functional（FF，完全可用）：同时有Manager、Cashier和Warehouse三类岗位的员工在岗；
- Not Functional（NF，不可用）：不满足上述任一条件。
需求与问题
需要统计不同时间段内的在岗人员对应的功能状态，生成符合要求的结果表。
此前尝试用自连接查询重叠时间，但只能处理两两重叠的场景，返回结果冗余且无法正确判断功能状态。尝试的SQL语句如下：
SELECT tc1.employee_job, tc2.employee_job, tc1.start_date_time, tc2.end_date_time FROM tctable tc1 LEFT OUTER JOIN tctable tc2 on ((tc1.start_date_time >= tc2.start_date_time AND tc1.start_date_time <= ISNULL(tc2.end_date_time,GETDATE())) OR (ISNULL(tc1.end_date_time,GETDATE()) <= ISNULL(tc2.end_datetime,GETDATE()) AND ISNULL(tc1.end_date_time, GETDATE()) >= tc2.start_date_time)) AND tc1.key <> tc2.key
求能处理任意数量重叠工时的SQL查询方案。
内容的提问来源于stack exchange，提问作者John Wesley Gordon
相关产品推荐
相关产品推荐
