求助：如何用SQL统计指定时间段内员工的缺勤天数？

问题描述 #

我有一张SQL表PersonTraffic，需要统计2025年4月1日至2025年7月1日期间员工的缺勤天数，期望得到如下目标结果表的数据，但当前编写的SQL查询仅能返回缺勤员工列表，无法达成统计缺勤天数的预期效果，请求帮助。

PersonTraffic表结构及数据 #

期望结果（2025年4月1日至2025年7月1日缺勤天数统计） #

当前使用的SQL查询（仅返回缺勤员工列表） #

DECLARE @date1 VARCHAR(10)='04/01/2025';
DECLARE @date2 VARCHAR(10)='07/01/2025';

USE [ARKS]

SELECT *  
FROM [dbo].[PersonTraffic] 
WHERE [PersonID] NOT IN (SELECT [PersonID] 
                         FROM [dbo].[PersonTraffic] 
                         WHERE [IO_DATE] BETWEEN @date1 AND @date2) 

解决方案：统计缺勤天数的SQL查询 #

要实现预期的缺勤天数统计，需要先生成统计时间段内的所有有效日期，再匹配每个员工在这些日期的出勤情况，最后统计未出勤的天数。以下是适配需求的SQL代码：

DECLARE @startDate DATE = '2025-04-01';
DECLARE @endDate DATE = '2025-07-01';

USE [ARKS]

-- 生成统计时间段内的所有日期（若仅统计工作日需额外调整）
WITH DateRange AS (
    SELECT @startDate AS Date
    UNION ALL
    SELECT DATEADD(DAY, 1, Date)
    FROM DateRange
    WHERE Date < @endDate
),
-- 获取所有唯一员工信息
UniqueEmployees AS (
    SELECT DISTINCT PersonName, PersonID
    FROM [dbo].[PersonTraffic]
),
-- 生成每个员工对应所有统计日期的记录
EmployeeDatePairs AS (
    SELECT u.PersonName, u.PersonID, d.Date
    FROM UniqueEmployees u
    CROSS JOIN DateRange d
),
-- 标记员工在对应日期是否出勤
AttendanceStatus AS (
    SELECT 
        ed.PersonName,
        ed.PersonID,
        ed.Date,
        CASE WHEN pt.IO_DATE IS NOT NULL THEN 1 ELSE 0 END AS IsPresent
    FROM EmployeeDatePairs ed
    LEFT JOIN (
        SELECT DISTINCT PersonID, IO_DATE
        FROM [dbo].[PersonTraffic]
        WHERE CONVERT(DATE, IO_DATE, 101) BETWEEN @startDate AND @endDate
    ) pt ON ed.PersonID = pt.PersonID AND ed.Date = CONVERT(DATE, pt.IO_DATE, 101)
)
-- 统计每个员工的缺勤天数
SELECT 
    PersonName,
    PersonID,
    COUNT(CASE WHEN IsPresent = 0 THEN 1 END) AS [Count of Absent day]
FROM AttendanceStatus
GROUP BY PersonName, PersonID
ORDER BY PersonName;

代码说明 #

1. DateRange CTE：生成从起始日期到结束日期的所有连续日期，作为统计的基准日期列表。
2. UniqueEmployees CTE：提取PersonTraffic表中所有唯一的员工姓名和ID，确保每个员工都被统计到。
3. EmployeeDatePairs CTE：通过交叉连接员工列表和日期范围，得到每个员工在每个统计日期的对应记录。
4. AttendanceStatus CTE：左连接去重后的出勤记录，标记每个员工在对应日期是否有出勤（当天有任何一条记录即视为出勤）。
5. 最后通过分组统计，计算每个员工未出勤的日期数量，即缺勤天数。

注：如果仅需要统计工作日的缺勤天数，需在DateRange中添加工作日判断逻辑（例如排除周六、周日及法定节假日）。

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