跨天班次员工打卡间隔计算与打卡状态判定SQL查询问题

核心问题根因 #

原有查询使用trunc(Timeinout)（打卡自然日）作为LEAD/LAG的分区字段，仅能归集当日打卡的单日班次数据，跨天班次中次日凌晨的打卡会被划分到下一个自然日分区，导致同归属考勤日的打卡被拆分，间隔计算和状态判定全部错误。

修正思路 #

利用表中已有的flag字段计算归属考勤日作为分区依据：

• flag=0：打卡归属当天，归属考勤日 = 打卡自然日
• flag=1：打卡归属前一天，归属考勤日 = 打卡自然日 - 1天
  统一计算逻辑为：TRUNC(Timeinout) - flag

修正后完整SQL #

SELECT X.*,
       CASE
         WHEN Previous_Movment IS NULL AND Next_Movment IS NOT NULL THEN
          '首卡'
         WHEN Previous_Movment IS NOT NULL AND Next_Movment IS NULL THEN
          '末卡'
         WHEN Previous_Movment IS NULL AND Next_Movment IS NULL THEN
          '当日无打卡'
         ELSE
          '日间打卡'
       END CHECK_STATUS
  FROM (SELECT 
               -- 替换为归属考勤日，不再使用打卡自然日
               TRUNC(a.timeinout) - a.flag AS attendance_date,
               a.timeinout current_movment,
               -- 分区字段替换为归属考勤日
               LEAD(TIMEINOUT) OVER(PARTITION BY TRUNC(a.timeinout) - a.flag, Emp_ID ORDER BY TIMEINOUT) Next_Movment,
               LAG(TIMEINOUT) OVER(PARTITION BY TRUNC(a.timeinout) - a.flag, Emp_ID ORDER BY TIMEINOUT) Previous_Movment,
               -- 时间间隔计算逻辑保持不变，仅调整分区字段
               TRUNC(24 *
                     MOD(LEAD(TIMEINOUT) OVER(PARTITION BY TRUNC(a.timeinout) - a.flag,
                              Emp_ID ORDER BY TIMEINOUT) - TIMEINOUT,
                         1)) AS diff_hours,
               TRUNC(MOD(MOD(LEAD(TIMEINOUT)
                             OVER(PARTITION BY TRUNC(a.timeinout) - a.flag,
                                  Emp_ID ORDER BY TIMEINOUT) - TIMEINOUT,
                             1) * 24,
                         1) * 60) AS diff_minus,
               TRUNC(MOD(MOD(MOD(LEAD(TIMEINOUT)
                                 OVER(PARTITION BY TRUNC(a.timeinout) - a.flag,
                                      Emp_ID ORDER BY TIMEINOUT) - TIMEINOUT,
                                 1) * 24,
                             1) * 60,
                         1) * 60) AS diff_sec,
               Emp_ID,
               FLAG Return_Previous_day_or_not
          FROM My_table a
         WHERE TRUNC(a.timeinout) BETWEEN
               TO_DATE('03-07-2018', 'dd-mm-yyyy') AND
               TO_DATE('07-07-2018', 'dd-mm-yyyy')
          -- 可根据需要放开/调整员工过滤条件
          -- AND a.Emp_ID = 2
        ) X
ORDER BY X.EMP_ID, X.attendance_date, X.current_movment

效果验证 #

以测试数据中员工1的2018-07-03考勤日为例：

• 会同时归集7月3日16:39（flag=0）、7月4日01:14:40（flag=1）、7月4日01:14:44（flag=1）三条同归属日的打卡记录
• 相邻间隔计算正常，首卡为7月3日16:39的记录，末卡为7月4日01:14:44的记录，完全符合跨天班次的考勤规则
• 单日班次的员工2的计算逻辑与原有正确结果完全一致，无需额外调整

内容的提问来源于stack exchange，提问作者M.Youssef