如何修改SQL考勤查询语句以展示缺勤工作日？

现有SQL可查询员工考勤记录，但无法展示缺勤的工作日，需修改语句将缺勤日期纳入结果并标注为absent。

当前查询结果 #

Userid  Date        TimeIn   TimeOut    Remark
7       7/25/2023   7:55:14             Didn't Clock Out
7       7/24/2023   7:41:27  17:07:59   
7       7/21/2023   7:47:03  17:17:37   
7       7/20/2023   7:51:43  17:04:53   
7       7/19/2023   7:53:53  17:07:28   
7       7/14/2023   8:47:35  17:17:15   
7       7/13/2023   8:36:06  17:07:25   
7       7/12/2023   7:49:26  17:05:15   
7       7/7/2023    7:50:55  17:03:52   
7       7/6/2023    7:52:57  17:11:54

期望查询结果 #

Userid  Date        TimeIn    TimeOut    Remark
7       7/25/2023   7:55:14              Didn't Clock Out
7       7/24/2023   7:41:27   17:07:59  
7       7/21/2023   7:47:03   17:17:37  
7       7/20/2023   7:51:43   17:04:53  
7       7/19/2023   7:53:53   17:07:28  
7       7/18/2023                        absent
7       7/17/2023                        absent
7       7/14/2023   8:47:35   17:17:15  
7       7/13/2023   8:36:06   17:07:25  
7       7/12/2023   7:49:26   17:05:15  
7       7/7/2023    7:50:55   17:03:52  
7       7/6/2023    7:52:57   17:11:54

现有SQL代码 #

SELECT UserID 'Userid', 
    CONVERT(DATE,TransactionTime) 'Date',
    MIN(CAST(CONVERT(TIME,TransactionTime) AS VARCHAR(8))) 'TimeIN',
    CASE WHEN MIN(CAST(CONVERT(TIME,TransactionTime) AS VARCHAR(8))) = MAX(CAST(CONVERT(TIME,TransactionTime) AS VARCHAR(8))) THEN 
        '' 
    ELSE
        MAX(CAST(CONVERT(TIME,TransactionTime) AS VARCHAR(8)))
    END 'TimeOut',
    CASE WHEN MIN(CAST(CONVERT(TIME,TransactionTime) AS VARCHAR(8))) = MAX(CAST(CONVERT(TIME,TransactionTime) AS VARCHAR(8))) THEN 
        'Didn''t Clock Out' 
    ELSE
        ''
    END 'Remark'
FROM NGAC_AUTHLOG WHERE UserID='0007'
GROUP BY UserID,DATEPART(MONTH,TransactionTime),CONVERT(DATE,TransactionTime)
Order by CONVERT(DATE,TransactionTime) desc

修改后的SQL代码 #

-- 生成指定范围内的所有工作日日期序列
WITH DateRange AS (
    SELECT CONVERT(DATE, '2023-07-06') AS WorkDate
    UNION ALL
    SELECT DATEADD(DAY, 1, WorkDate)
    FROM DateRange
    WHERE WorkDate < CONVERT(DATE, '2023-07-25')
),
WorkDays AS (
    SELECT WorkDate
    FROM DateRange
    -- 过滤周末：DATEPART(WEEKDAY, WorkDate) 1=周日，2=周一，...，7=周六
    WHERE DATEPART(WEEKDAY, WorkDate) NOT IN (1, 7)
),
-- 原考勤数据聚合
AttendanceData AS (
    SELECT 
        UserID,
        CONVERT(DATE, TransactionTime) AS AttendanceDate,
        MIN(CAST(CONVERT(TIME, TransactionTime) AS VARCHAR(8))) AS TimeIN,
        CASE WHEN MIN(CAST(CONVERT(TIME, TransactionTime) AS VARCHAR(8))) = MAX(CAST(CONVERT(TIME, TransactionTime) AS VARCHAR(8))) 
             THEN '' 
             ELSE MAX(CAST(CONVERT(TIME, TransactionTime) AS VARCHAR(8)))
        END AS TimeOut,
        CASE WHEN MIN(CAST(CONVERT(TIME, TransactionTime) AS VARCHAR(8))) = MAX(CAST(CONVERT(TIME, TransactionTime) AS VARCHAR(8))) 
             THEN 'Didn''t Clock Out' 
             ELSE ''
        END AS Remark
    FROM NGAC_AUTHLOG 
    WHERE UserID = '0007'
    GROUP BY UserID, CONVERT(DATE, TransactionTime)
)
-- 左连接工作日序列和考勤数据，补全缺勤记录
SELECT 
    '7' AS Userid,
    w.WorkDate AS Date,
    ISNULL(a.TimeIN, '') AS TimeIn,
    ISNULL(a.TimeOut, '') AS TimeOut,
    CASE 
        WHEN a.AttendanceDate IS NULL THEN 'absent'
        ELSE a.Remark
    END AS Remark
FROM WorkDays w
LEFT JOIN AttendanceData a ON w.WorkDate = a.AttendanceDate
ORDER BY w.WorkDate DESC

关键说明 #

1. 生成日期序列：通过递归CTE DateRange生成指定起始和结束日期之间的所有日期，再用WorkDays过滤掉周末，得到所有工作日。
2. 聚合原考勤数据：保留原查询的逻辑，将考勤记录按日期聚合，得到每个工作日的上下班时间和备注。
3. 左连接补全缺勤：将工作日序列与聚合后的考勤数据左连接，考勤数据为空的日期即为缺勤，标注absent。

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