计算员工月度缺勤时如何在SQL中排除周日不计入核算

• 现存问题：现有SQL核算员工月度缺勤天数时，误将周日纳入统计范围，不符合“周日为非工作日、不计入缺勤核算”的业务规则
• 偏差表现：针对员工Susan的统计返回结果为缺勤1天，按业务规则正确结果应为0，偏差来源是当月截至统计时点的1个周日被误纳入应出勤基数

涉及的数据表结构 #

• emp0003 员工基础信息表：存储员工核心身份字段
  • 字段：NIK（员工编号）、Name（员工姓名）
  • 示例数据：编号01190301对应用户为Susan
• dvc0004 打卡记录表：存储员工每日上下班打卡的时间戳记录
  • 字段：NIK（关联员工编号）、Enroll（打卡时间戳）
  • 示例数据：存储了员工01190301在2021年1月30日-2月8日区间的多组上下班打卡记录

当前存在逻辑缺陷的SQL语句 #

SELECT
    emp0003.NIK,
    emp0003.`Name`,
    DAY(CURRENT_DATE) - COUNT(
        DISTINCT
        DATE(LEFT((`dvc0004`.`Enroll`), 10)),
        (CASE
            WHEN LEFT(( `dvc0004`.`Enroll`), 10) 
                AND MONTH(LEFT((`dvc0004`.`Enroll`), 10)) = MONTH(CURRENT_DATE()) 
                AND YEAR(LEFT((`dvc0004`.`Enroll`), 10)) = YEAR(CURRENT_DATE())
            THEN 1 
        END)
    ) AS 'TOTAL ABSENT OF THE MONTH'            
FROM emp0003
LEFT JOIN dvc0004 ON emp0003.NIK = dvc0004.NIK      
WHERE emp0003.nik = '01190301' 

逻辑偏差核心原因 #

• 应出勤天数直接取DAY(CURRENT_DATE)，未剔除统计周期内的周日非工作日，导致核算基数偏大
• COUNT(DISTINCT ...)语法逻辑错误，同时传入日期值和CASE判断值，无法正确统计员工的有效打卡天数
• 全流程未对周日做规则过滤，导致属于非工作日的周日被误判定为缺勤天

修正方向 #

• 计算应出勤基数时，逐天判断统计周期内的日期，仅保留非周日的工作日天数作为核算基数
• 调整有效打卡天数的统计逻辑：仅匹配当月、非周日的打卡记录做去重计数
• 最终缺勤天数 = 统计周期内的应出勤工作日天数 - 员工实际有效打卡的工作日天数

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