如何在SQL查询中补全datec列的缺失日期？

问题背景 #

现有SQL查询可返回指定用户的考勤记录，但仅展示存在考勤数据的日期，需要补全所有缺失日期——即使当天无任何考勤记录，也要显示该日期，对应考勤字段填充为NULL。

原查询SQL：

select 
    u.NAME, u.BADGENUMBER, 
    attendance.SENSORID, attendance.CHECKDate, 
    attendance.CheckIn ,attendance.CheckOut,
    cast(DATEDIFF(n, attendance.CheckIn, attendance.checkout) / 60 as varchar) + ':' + cast(DATEDIFF(n, attendance.CheckIn, attendance.checkout) % 60 as varchar) as Minutes
from 
    (select 
         temp.USERID, temp.SENSORID, temp.CHECKDate ,temp.CheckIn,
         case 
             when temp.COut = temp.CheckIn then null 
             when temp.CheckIn is null then null 
             else temp.COut 
         end as CheckOut
     from
         (select 
              c.USERID, c.SENSORID, convert(date, CHECKTIME) CHECKDate,
              (select min(CHECKTIME) from CHECKINOUT cinout 
               where cinout.USERID = c.USERID and cinout.SENSORID = c.SENSORID 
                 and cinout.CheckTime >= dateadd(hour, 6, convert(datetime, convert(date, c.CheckTime)))) CheckIn,
              (select max(CHECKTIME) from CHECKINOUT cinout 
               where cinout.USERID = c.USERID and cinout.SENSORID = c.SENSORID 
                 and cinout.CheckTime <= dateadd(hour, 29, convert(datetime, convert(date, c.CheckTime)))) COut
          from 
              CHECKINOUT c
          group by 
              c.USERID, c.SENSORID, convert(date, CHECKTIME), datename(DW, checktime)) temp
       ) attendance 
inner join  
     userinfo u on u.USERID = attendance.userid 
where 
     u.USERID = 77 

当前输出（仅显示有考勤的日期）：

Name  Badg  Sen CheckDate  CheckIn               CheckOut             Minutes
Umair 77    1   2022-11-16 2022-11-16 09:41:25.000 2022-11-16 18:45:46.000 9:4
Umair 77    1   2022-11-21 2022-11-21 09:29:29.000 2022-11-21 19:00:33.000 9:31
Umair 77    1   2022-11-22 2022-11-22 09:25:19.000 2022-11-22 18:26:38.000 9:1

解决方案 #

核心思路是生成连续日期序列，再与用户、传感器信息做交叉连接，最后左连接原考勤数据，确保所有日期都被包含。

修改后的完整SQL #

-- 生成连续日期的CTE，覆盖从用户最早考勤日期到最晚考勤日期的范围
WITH DateRange AS (
    SELECT MIN(convert(date, CHECKTIME)) AS DateVal
    FROM CHECKINOUT
    WHERE USERID = 77
    UNION ALL
    SELECT DATEADD(day, 1, DateVal)
    FROM DateRange
    WHERE DateVal < (SELECT MAX(convert(date, CHECKTIME)) FROM CHECKINOUT WHERE USERID = 77)
),
-- 获取用户和对应的传感器（如果用户固定使用某个传感器，可直接写死SENSORID=1）
UserSensor AS (
    SELECT u.USERID, u.NAME, u.BADGENUMBER, s.SENSORID
    FROM userinfo u
    CROSS JOIN (SELECT DISTINCT SENSORID FROM CHECKINOUT WHERE USERID = 77) s
    WHERE u.USERID = 77
),
-- 封装原有的考勤逻辑
AttendanceData AS (
    SELECT 
        temp.USERID, temp.SENSORID, temp.CHECKDate, temp.CheckIn,
        CASE 
            WHEN temp.COut = temp.CheckIn THEN NULL 
            WHEN temp.CheckIn IS NULL THEN NULL 
            ELSE temp.COut 
        END AS CheckOut
    FROM (
        SELECT 
            c.USERID, c.SENSORID, convert(date, CHECKTIME) CHECKDate,
            (SELECT MIN(CHECKTIME) FROM CHECKINOUT cinout 
             WHERE cinout.USERID = c.USERID AND cinout.SENSORID = c.SENSORID 
               AND cinout.CheckTime >= DATEADD(hour, 6, CONVERT(datetime, CONVERT(date, c.CheckTime)))) CheckIn,
            (SELECT MAX(CHECKTIME) FROM CHECKINOUT cinout 
             WHERE cinout.USERID = c.USERID AND cinout.SENSORID = c.SENSORID 
               AND cinout.CheckTime <= DATEADD(hour, 29, CONVERT(datetime, CONVERT(date, c.CheckTime)))) COut
        FROM CHECKINOUT c
        WHERE c.USERID = 77
        GROUP BY c.USERID, c.SENSORID, convert(date, CHECKTIME), datename(DW, checktime)
    ) temp
)
-- 左连接所有日期、用户传感器与考勤数据
SELECT 
    us.NAME, us.BADGENUMBER,
    us.SENSORID, dr.DateVal AS CHECKDate,
    ad.CheckIn, ad.CheckOut,
    -- 无考勤时Minutes显示空字符串
    CASE 
        WHEN ad.CheckIn IS NULL OR ad.CheckOut IS NULL THEN ''
        ELSE CAST(DATEDIFF(n, ad.CheckIn, ad.CheckOut) / 60 AS VARCHAR) + ':' + CAST(DATEDIFF(n, ad.CheckIn, ad.CheckOut) % 60 AS VARCHAR)
    END AS Minutes
FROM DateRange dr
CROSS JOIN UserSensor us
LEFT JOIN AttendanceData ad 
    ON dr.DateVal = ad.CHECKDate 
    AND us.USERID = ad.USERID 
    AND us.SENSORID = ad.SENSORID
ORDER BY dr.DateVal;

关键说明 #

1. DateRange CTE：递归生成用户最早到最晚考勤日期之间的所有连续日期，确保覆盖需要补全的日期范围。
2. UserSensor CTE：获取指定用户及其对应的所有传感器，避免遗漏该用户可能使用的考勤设备。
3. AttendanceData CTE：封装原考勤逻辑，简化主查询结构。
4. 左连接：通过日期序列与用户传感器的交叉连接，得到所有日期+用户+传感器的组合，再左连接考勤数据，无考勤记录的日期自动填充NULL。
5. Minutes字段处理：无考勤数据时，将Minutes设为空字符串，避免因NULL值导致的计算错误。

预期输出示例 #

Name  Badg  Sen CheckDate  CheckIn               CheckOut             Minutes
Umair 77    1   2022-11-16 2022-11-16 09:41:25.000 2022-11-16 18:45:46.000 9:4
Umair 77    1   2022-11-17 NULL                  NULL                 
Umair 77    1   2022-11-18 NULL                  NULL                 
Umair 77    1   2022-11-19 NULL                  NULL                 
Umair 77    1   2022-11-20 NULL                  NULL                 
Umair 77    1   2022-11-21 2022-11-21 09:29:29.000 2022-11-21 19:00:33.000 9:31
Umair 77    1   2022-11-22 2022-11-22 09:25:19.000 2022-11-22 18:26:38.000 9:1

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