如何合并员工工作日与休假记录生成月度全量考勤报表？

需求说明 #

现有两张数据表：

• timesheet：存储员工每日登录记录，包含字段如user_id、start_time_server（登录时间）等
• leaves：存储员工休假日期记录，包含字段如user_id、start_time_server（休假日期）等

需要生成月度报表，展示每位员工当月每一天的出勤状态（工作/休假/缺勤）。

现有代码问题分析 #

你当前的SQL存在以下问题：

• 滥用游标，效率低下且逻辑冗余（已指定user_id=679，却仍用游标遍历）
• 未生成当月完整日期列表，会缺失无登录也无休假的日期
• 临时表使用逻辑混乱，存在不必要的嵌套查询
• 最后查询使用笛卡尔积（@ts,@le_ts,timesheet），会生成大量重复数据
• 重复过滤user_id=679，逻辑冗余

正确实现思路 #

1. 生成当月所有日期：先构造包含当月每一天的日期序列，确保报表覆盖全月
2. 关联员工列表：将所有员工与当月日期做笛卡尔积，得到每位员工对应当月每天的基础记录
3. 匹配出勤/休假记录：通过左关联分别匹配timesheet和leaves表，判断当天状态
4. 状态优先级处理：根据业务规则确定状态优先级（比如休假优先于工作，若员工当天既休假又登录，仍标记为休假）

完整SQL实现 #

-- 1. 生成当月所有日期的临时表
DECLARE @StartDate DATE = DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1)
DECLARE @EndDate DATE = EOMONTH(GETDATE())

;WITH DateRange AS (
    SELECT @StartDate AS [Date]
    UNION ALL
    SELECT DATEADD(DAY, 1, [Date])
    FROM DateRange
    WHERE [Date] < @EndDate
),
-- 2. 获取所有需要统计的员工（可根据业务调整过滤条件）
EmployeeList AS (
    SELECT USER_ID, USER_NAME -- 假设users表有用户名字段，可根据实际调整
    FROM users
    -- WHERE user_id=679 -- 如果只统计单个员工，可打开此注释
)
-- 3. 关联生成最终报表
SELECT
    el.USER_ID,
    el.USER_NAME,
    dr.[Date],
    -- 根据匹配结果判断出勤状态
    CASE
        WHEN l.user_id IS NOT NULL THEN '休假'
        WHEN t.user_id IS NOT NULL THEN '工作'
        ELSE '缺勤'
    END AS AttendanceStatus
FROM DateRange dr
CROSS JOIN EmployeeList el
LEFT JOIN leaves l 
    ON el.USER_ID = l.user_id 
    AND CONVERT(DATE, l.start_time_server) = dr.[Date]
LEFT JOIN timesheet t 
    ON el.USER_ID = t.user_id 
    AND CONVERT(DATE, t.start_time_server) = dr.[Date]
ORDER BY el.USER_ID, dr.[Date]

代码说明 #

• DateRange：用递归CTE生成当月从1号到月末的所有日期
• EmployeeList：获取需要统计的员工列表，可按需添加过滤条件
• CROSS JOIN：将员工列表与日期列表关联，生成每位员工对应当月每天的基础行
• LEFT JOIN leaves/timesheet：匹配当天是否有休假或登录记录
• CASE语句：根据匹配结果输出状态，优先级为休假 > 工作 > 缺勤，可根据业务调整顺序

注意事项 #

• 如果timesheet中存在员工一天多次登录的情况，需先做去重处理（比如用DISTINCT或分组），避免生成重复行
• 若leaves表的休假是时间段（含开始/结束日期），需调整关联条件为dr.[Date] BETWEEN CONVERT(DATE, l.start_time) AND CONVERT(DATE, l.end_time)
• 可根据实际需求添加更多字段（如登录时长、休假类型等）

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