员工休息违规检测SQL逻辑咨询：多休息时长校验

需求背景 #

• 需检测员工休息违规行为，针对两类班次时长执行校验规则：
  a. 班次时长>6小时且<10小时
  b. 班次时长>10小时，需重点检查休息次数
• 针对班次时长>6小时且<10小时的具体规则：
  1. 单日仅1次休息时：需校验首次休息是否在班次开始后第6小时结束前启动，且休息时长≥30分钟
  2. 单日有2次及以上休息时：需校验是否存在至少1次休息时长≥30分钟，满足则判定无违规，否则判定违规
• 现有数据情况：2023年2月6、7、8日该员工单日各有1次休息，9日有2次休息
• 当前已编写包含datestart、dateend、empid参数的SQL存储过程（参数示例：datestart='2023-02-06'，dateend='2023-02-09'），但无法实现识别2次及以上休息场景并校验是否存在≥30分钟休息的逻辑

现有SQL代码 #

select datestart
       ,DateEnd
       ,BREAKLENGTH
        ,MINSELAPSED
        ,ROW_NUMBER() OVER (PARTITION BY EMPID,CAST(DATESTART AS DATE) ORDER BY DATESTART ASC) BREAKCOUNT from(
select EMPID 
      ,datestart
      , DATEEND, 
      LAG_DATEEND
      ,DATEDIFF(MI,LAG_DATEEND,DateStart) AS BREAKLENGTH
     ,CAST(ROUND(MINSELAPSED/60.0,2) AS NUMERIC(36,2)) AS MINSELAPSED from (
select empid,
        datestart,
        DATEEND,
        LAG(DATEEND,1,NULL) OVER (PARTITION BY EMPID,CAST(DATESTART AS DATE) ORDER BY DATESTART ASC) AS LAG_DATEEND,
        MinsElapsed
         from coemplbr
where empid = '8371' and DateStart between '2023-02-06' and '2023-02-10' and ItmTyp = 362)a)B
--where BREAKLENGTH > 30
GROUP BY DATESTART,DateEnd,BREAKLENGTH,MINSELAPSED,EMPID

查询结果 #

解决方案 #

通过分层CTE（公共表表达式）先统计单日休息的核心指标，再应用违规判定规则：

WITH break_details AS (
    -- 计算单次休息时长、班次时间范围
    SELECT 
        empid,
        CAST(datestart AS DATE) AS work_date,
        datestart,
        dateend,
        -- 计算休息时长（上一段工作结束到当前休息后上班的间隔）
        DATEDIFF(MI, LAG(dateend, 1, NULL) OVER (PARTITION BY empid, CAST(datestart AS DATE) ORDER BY datestart ASC), datestart) AS break_length,
        -- 标记首次上班时间，用于校验休息启动时间
        MIN(datestart) OVER (PARTITION BY empid, CAST(datestart AS DATE)) AS shift_start_time
    FROM coemplbr
    WHERE empid = '8371' 
      AND datestart BETWEEN '2023-02-06' AND '2023-02-10' 
      AND ItmTyp = 362
),
daily_break_stats AS (
    -- 按日期聚合休息统计数据
    SELECT 
        empid,
        work_date,
        COUNT(*) AS total_breaks,
        MAX(CASE WHEN break_length >= 30 THEN 1 ELSE 0 END) AS has_valid_long_break,
        -- 计算班次总时长（分钟）
        DATEDIFF(MI, MIN(datestart), MAX(dateend)) AS shift_total_mins
    FROM break_details
    GROUP BY empid, work_date
)
-- 最终违规判定
SELECT 
    empid,
    work_date,
    shift_total_mins,
    total_breaks,
    has_valid_long_break,
    CASE 
        -- 处理6-10小时班次的规则
        WHEN shift_total_mins > 360 AND shift_total_mins < 600 THEN
            CASE 
                WHEN total_breaks = 1 THEN
                    -- 校验首次休息是否在6小时内启动且时长≥30分钟
                    CASE WHEN EXISTS (
                        SELECT 1 FROM break_details bd
                        WHERE bd.empid = dbs.empid AND bd.work_date = dbs.work_date
                          AND bd.break_length >= 30
                          AND DATEDIFF(MI, bd.shift_start_time, bd.datestart) <= 360
                    ) THEN '无违规' ELSE '违规' END
                WHEN total_breaks >= 2 THEN
                    CASE WHEN has_valid_long_break = 1 THEN '无违规' ELSE '违规' END
                ELSE '无违规'
            END
        -- 处理>10小时班次的规则（可根据需求补充具体逻辑）
        WHEN shift_total_mins >= 600 THEN '需检查休息次数'
        ELSE '无需校验'
    END AS violation_status
FROM daily_break_stats
ORDER BY work_date;

逻辑说明 #

1. break_details层：计算单次休息时长、班次启动时间，为后续校验提供基础数据
2. daily_break_stats层：按员工+日期聚合，得到单日休息总次数、是否存在≥30分钟的休息、班次总时长
3. 最终查询：根据班次时长和休息次数，分别应用对应规则判定违规状态

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