基于SQL生成劳动力管理排班依从性报告的技术咨询

最近我在做劳动力管理领域的排班依从性（Schedule Adherence）报告，用SQL来处理数据，核心依赖两张表：dbo.Scheduled_Shifts（计划班次表）和dbo.Actual_Work（实际工作记录表）。先给大家展示下这两张表的示例数据：

1. 计划班次表：dbo.Scheduled_Shifts #

这张表存储了员工的计划排班信息，包括工作、休息、午餐等时段：

（注：原示例最后一行未完成，这里补充了合理的结束时间）

2. 实际工作记录表：dbo.Actual_Work #

这张表记录了员工实际的工作/休息时段，用于和计划对比：

3. 核心SQL实现思路 #

排班依从性的核心是计算实际行为与计划的匹配时长占总计划时长的比例。针对这个需求，我写了一段SQL，通过CTE分步处理数据：

WITH Scheduled_With_Date AS (
    -- 提取计划班次的日期和时长（分钟）
    SELECT 
        EmpID,
        ScheduleType,
        CAST(StartTime AS DATE) AS ShiftDate,
        DATEDIFF(MINUTE, StartTime, EndTime) AS ScheduledDuration
    FROM dbo.Scheduled_Shifts
),
Actual_With_Date AS (
    -- 提取实际工作的日期和时长（分钟）
    SELECT 
        EmpID,
        ActivityType,
        CAST(StartTime AS DATE) AS WorkDate,
        DATEDIFF(MINUTE, StartTime, EndTime) AS ActualDuration
    FROM dbo.Actual_Work
),
Shift_Match AS (
    -- 关联计划和实际，计算每个时段的依从时长
    SELECT 
        s.EmpID,
        s.ShiftDate,
        s.ScheduleType,
        s.ScheduledDuration,
        ISNULL(a.ActualDuration, 0) AS ActualDuration,
        -- 计算计划与实际重叠的时长（仅当班次类型一致时）
        CASE 
            WHEN s.ScheduleType = a.ActivityType THEN 
                DATEDIFF(MINUTE, 
                    GREATEST(a.StartTime, s.StartTime),
                    LEAST(a.EndTime, s.EndTime)
                )
            ELSE 0
        END AS AdherentDuration
    FROM Scheduled_With_Date s
    LEFT JOIN dbo.Actual_Work a 
        ON s.EmpID = a.EmpID 
        AND s.ShiftDate = CAST(a.StartTime AS DATE)
        AND s.ScheduleType = a.ActivityType
        AND a.StartTime < s.EndTime 
        AND a.EndTime > s.StartTime -- 过滤无重叠的时段
)
-- 最终统计每个员工每日的依从率
SELECT 
    EmpID,
    ShiftDate,
    SUM(ScheduledDuration) AS TotalScheduledMinutes,
    SUM(AdherentDuration) AS TotalAdherentMinutes,
    -- 计算依从率，保留两位小数，避免除以0的情况
    ROUND(CASE WHEN SUM(ScheduledDuration) = 0 THEN 0 ELSE CAST(SUM(AdherentDuration) AS FLOAT) / SUM(ScheduledDuration) * 100 END, 2) AS ScheduleAdherenceRate
FROM Shift_Match
GROUP BY EmpID, ShiftDate
ORDER BY EmpID, ShiftDate;

SQL逻辑说明 #

1. Scheduled_With_Date：把计划班次按日期拆分，计算每个时段的计划时长（分钟），方便后续统计。
2. Actual_With_Date：同理处理实际工作记录，提取日期和实际时长。
3. Shift_Match：关联计划和实际记录，匹配相同员工、日期、班次类型且有时间重叠的记录，计算每个匹配时段的依从时长（即计划与实际重叠的部分）。
4. 最终统计：按员工和日期分组，计算总计划时长、总依从时长，最后算出依从率（加入了除以0的防护逻辑）。

这样就能得到每个员工每天的排班依从性数据，方便后续生成可视化报告。

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