基于SSRS创建考勤报表：SQL Server工作日计算问题求助

核心需求拆解 #

• 按员工+年月维度统计实际出勤天数（从tbl_EmpAttendance统计有效出勤记录）
• 计算每月应出勤工作日：排除周六周日，再减去tbl_Holiday中覆盖当月的假期天数
• 输出结果作为SSRS报表的视图数据源

优化后的实现方案 #

1. 整合工作日与假期计算逻辑 #

替换原标量函数fn_GetWorkDays，改用基于集合的CTE计算，同时处理跨月假期：

CREATE VIEW vw_EmployeeAttendanceReport
AS
WITH DateRange AS (
    -- 生成当年全年日期范围，自动适配当前年份
    SELECT DATEADD(day, number, DATEFROMPARTS(YEAR(GETDATE()), 1, 1)) AS DateVal
    FROM master.dbo.spt_values
    WHERE type = 'P'
    AND DATEADD(day, number, DATEFROMPARTS(YEAR(GETDATE()), 1, 1)) <= DATEFROMPARTS(YEAR(GETDATE()), 12, 31)
),
WorkDaysBase AS (
    -- 筛选非周末日期，注意：DATEPART(weekday)取值依赖SQL Server的DATEFIRST设置，若DATEFIRST=1则周末为6、7，需对应调整
    SELECT 
        YEAR(DateVal) AS YearVal,
        MONTH(DateVal) AS MonthVal,
        DateVal
    FROM DateRange
    WHERE DATEPART(weekday, DateVal) NOT IN (1,7) -- 1=周日，7=周六
),
HolidayDates AS (
    -- 展开所有假期日期（处理跨月/跨年的假期区间）
    SELECT 
        DATEADD(day, number, h.Fromdate) AS HolidayDate
    FROM tbl_Holiday h
    JOIN master.dbo.spt_values v ON type = 'P'
    AND DATEADD(day, number, h.Fromdate) <= h.ToDate
),
MonthlyWorkDays AS (
    -- 计算每月应出勤工作日：非周末天数 - 假期天数
    SELECT 
        YearVal,
        MonthVal,
        COUNT(w.DateVal) - COUNT(h.HolidayDate) AS TotalWorkDays
    FROM WorkDaysBase w
    LEFT JOIN HolidayDates h ON w.DateVal = h.HolidayDate
    GROUP BY YearVal, MonthVal
),
AllEmpMonth AS (
    -- 生成所有员工+全年12个月的完整组合，确保报表无缺失月份
    SELECT 
        e.EmpId,
        e.EmpName,
        y.YearVal,
        m.MonthVal
    FROM tbl_Employee e
    CROSS JOIN (SELECT DISTINCT YearVal FROM MonthlyWorkDays) y
    CROSS JOIN (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12)) AS m(MonthVal)
),
EmpMonthlyAttendance AS (
    -- 统计员工每月实际出勤天数，按日期去重避免重复统计
    SELECT 
        e.EmpId,
        e.EmpName,
        YEAR(a.ADate) AS YearVal,
        MONTH(a.ADate) AS MonthVal,
        COUNT(DISTINCT a.ADate) AS WorkedDays
    FROM tbl_Employee e
    LEFT JOIN tbl_EmpAttendance a ON e.RecId = a.EmpRecId
    WHERE a.ADate IS NOT NULL
    GROUP BY e.EmpId, e.EmpName, YEAR(a.ADate), MONTH(a.ADate)
)
-- 最终整合结果
SELECT 
    a.EmpName,
    a.EmpId,
    a.YearVal AS [Year],
    a.MonthVal AS [Month],
    ISNULL(att.WorkedDays, 0) AS WorkedDays,
    md.TotalWorkDays AS WorkingDays
FROM AllEmpMonth a
LEFT JOIN EmpMonthlyAttendance att 
    ON a.EmpId = att.EmpId 
    AND a.YearVal = att.YearVal 
    AND a.MonthVal = att.MonthVal
LEFT JOIN MonthlyWorkDays md 
    ON a.YearVal = md.YearVal 
    AND a.MonthVal = md.MonthVal;

关键优化点 #

• 性能提升：用集合式CTE替代标量函数，避免逐行计算的性能损耗
• 维度完整性：通过CROSS JOIN生成员工+全年12个月的组合，确保SSRS报表不会缺失无出勤的月份
• 假期精准计算：展开假期区间的所有日期，完美处理跨月/跨年的假期
• 出勤去重：用COUNT(DISTINCT a.ADate)避免同一员工同一天多条打卡记录重复统计

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