动态生成月度考勤透视表：天数适配与状态替换问题

嘿，我来帮你搞定这两个问题！咱们先拆解一下：动态生成对应月份天数的列，以及把周日设为'S'、null替换成'P'，这俩都得靠动态SQL结合日期生成来解决，静态Pivot肯定搞不定动态列的需求。

核心思路 #

1. 动态生成列：先计算目标月份的总天数，然后自动拼接出Pivot需要的列名（比如[1],[2]...[30]）。
2. 补全所有学生的每日记录：用学生表和目标月份的所有日期做交叉连接，确保每个学生每天都有一条记录，这样就不会漏掉出勤的学生。
3. 处理周日和出勤状态：判断当日是否为周日，是就设为'S'；否则如果考勤表有缺勤记录就用原Status，没有就设为'P'。

完整SQL代码 #

DECLARE @Dated DATE = '2024-05-01'; -- 这里传入你要查询的月份日期（任意一天都行）
DECLARE @DaysInMonth INT = DAY(EOMONTH(@Dated));
DECLARE @PivotColumns NVARCHAR(MAX);
DECLARE @SQL NVARCHAR(MAX);

-- 第一步：生成动态Pivot需要的列名（[1], [2], ..., [当月天数]）
WITH Numbers AS (
    SELECT 1 AS DayNum
    UNION ALL
    SELECT DayNum + 1 FROM Numbers WHERE DayNum < @DaysInMonth
)
SELECT @PivotColumns = STRING_AGG(QUOTENAME(DayNum), ', ')
FROM Numbers;

-- 第二步：构建动态SQL语句
SET @SQL = N'
WITH MonthDates AS (
    -- 生成目标月份的所有日期，以及对应的日期号（1-31）
    SELECT 
        DATEADD(DAY, n.DayNum - 1, DATEFROMPARTS(YEAR(@Dated), MONTH(@Dated), 1)) AS AttDate,
        n.DayNum
    FROM (
        SELECT 1 AS DayNum
        UNION ALL
        SELECT DayNum + 1 FROM Numbers WHERE DayNum < @DaysInMonth
    ) n
),
StudentDailyAttendance AS (
    -- 关联所有学生和每日日期，计算最终的考勤状态
    SELECT 
        s.fkStudentID,
        md.DayNum,
        -- 兼容不同DATEFIRST设置，准确识别周日
        CASE 
            WHEN DATEPART(WEEKDAY, md.AttDate) = CASE WHEN @@DATEFIRST = 1 THEN 7 ELSE 1 END THEN ''S''
            ELSE ISNULL(a.Status, ''P'')
        END AS AttStatus
    FROM 
        -- 这里用学生表获取所有学生，如果没有单独的学生表，可以换成(SELECT DISTINCT fkStudentID FROM tblAttendance WHERE fkClassID = 1)
        tblStudents s 
    CROSS JOIN MonthDates md
    LEFT JOIN tblAttendance a 
        ON s.fkStudentID = a.fkStudentID
        AND md.AttDate = a.atDate
        AND a.fkClassID = 1 -- 你的班级过滤条件
)
-- 最后做Pivot转换
SELECT fkStudentID, ' + @PivotColumns + N'
FROM StudentDailyAttendance
PIVOT (
    MAX(AttStatus) FOR DayNum IN (' + @PivotColumns + N')
) AS pvt;';

-- 执行动态SQL
EXEC sp_executesql @SQL, N'@Dated DATE', @Dated = @Dated;

关键细节说明 #

• 动态列生成：用递归CTE生成1到当月天数的数字序列，再用STRING_AGG拼接成Pivot需要的列名格式，完美适配不同月份的天数（包括2月的闰年情况）。
• 周日判断：用CASE WHEN @@DATEFIRST = 1 THEN 7 ELSE 1 END来兼容服务器的DATEFIRST设置（有些服务器把周一设为一周第一天，有些是周日），确保不会误判周日。
• 补全出勤记录：通过CROSS JOIN学生表和月份日期，保证每个学生每天都有记录，再用LEFT JOIN关联考勤表，没有缺勤记录的就用ISNULL替换成'P'。
• 班级过滤：把班级条件fkClassID = 1放在LEFT JOIN的ON子句里，避免过滤掉没有缺勤记录的学生。

如果你的数据库里没有单独的学生表，把代码里的tblStudents s替换成(SELECT DISTINCT fkStudentID FROM tblAttendance WHERE fkClassID = 1) s就行，这样只取该班级的所有学生。

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