CASE语句SQL语法错误排查：跨天考勤时间统计问题

一、语法错误排查 #

你写的错误查询有两个明显的语法问题，直接导致了"AS"附近的报错：

• CASE语句未闭合：每个CASE结构必须以END关键字收尾，你在ELSE MAX(ATT_TIME)后直接接AS IN_TIME，数据库无法识别CASE的结束位置。
• 多余的列分隔符：第二个CASE语句末尾的逗号（AS OUT_TIME,）是多余的，因为后面紧接着INTO关键字，不需要这个分隔符。

二、修正后的基础查询 #

先修复语法错误，同时微调逻辑适配跨天场景的基本判断，得到可以正常运行的查询：

SELECT EMP_NUMBER, ATT_DATE,
       CASE 
           WHEN MIN(ATT_TIME) >= '12:00' THEN MIN(ATT_TIME) 
           ELSE MAX(ATT_TIME) 
       END AS IN_TIME,
       CASE 
           WHEN MAX(ATT_TIME) < '12:00' THEN MAX(ATT_TIME) 
           ELSE MIN(ATT_TIME) 
       END AS OUT_TIME
INTO TABLE1
FROM FJ
GROUP BY EMP_NUMBER, ATT_DATE

三、跨天考勤的逻辑优化 #

不过上面的基础查询还没法完美处理跨天跨日期的考勤场景：比如员工在01-03-2018 22:00打卡上班，02-03-2018 17:00打卡下班，按ATT_DATE分组会拆成两条记录，无法关联为同一个考勤周期。

针对这种情况，我们可以通过合并日期时间、判断考勤周期的方式来处理：

WITH AttendanceWithDT AS (
    -- 合并日期和时间为完整的datetime字段
    SELECT 
        EMP_NUMBER,
        CAST(CONCAT(ATT_DATE, ' ', ATT_TIME) AS DATETIME) AS ATT_DATETIME,
        ATT_DATE
    FROM FJ
),
AttendanceWithInterval AS (
    -- 计算当前打卡与上一次打卡的时间间隔
    SELECT 
        EMP_NUMBER,
        ATT_DATETIME,
        ATT_DATE,
        DATEDIFF(HOUR, LAG(ATT_DATETIME) OVER (PARTITION BY EMP_NUMBER ORDER BY ATT_DATETIME), ATT_DATETIME) AS HOUR_DIFF
    FROM AttendanceWithDT
),
AttendanceWithCycle AS (
    -- 根据时间间隔划分考勤周期：间隔超过12小时则开启新周期
    SELECT 
        EMP_NUMBER,
        ATT_DATETIME,
        ATT_DATE,
        SUM(CASE WHEN HOUR_DIFF > 12 OR HOUR_DIFF IS NULL THEN 1 ELSE 0 END) 
            OVER (PARTITION BY EMP_NUMBER ORDER BY ATT_DATETIME) AS CYCLE_ID
    FROM AttendanceWithInterval
)
-- 按员工和考勤周期分组，得到完整的上下班记录
SELECT 
    EMP_NUMBER,
    MIN(ATT_DATE) AS ATT_START_DATE,
    MIN(ATT_DATETIME) AS IN_DATETIME,
    MAX(ATT_DATETIME) AS OUT_DATETIME
INTO TABLE1
FROM AttendanceWithCycle
GROUP BY EMP_NUMBER, CYCLE_ID

关键说明 #

• 如果你的ATT_TIME是原生时间类型，把'12:00'替换为CAST('12:00' AS TIME)会更严谨。
• 考勤周期的时间间隔阈值（12小时）可以根据你的实际业务规则调整。

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