如何在培训记录到期统计报表中包含无到期记录的月份？

你的问题核心在于原查询仅会返回存在培训到期记录的月份，要让指定日期区间内的所有月份都显示出来，我们需要先构建一个覆盖目标时间段的完整月份序列，再结合部门维度生成所有可能的部门-月份组合，最后左联你的培训记录统计数据即可。

具体实现步骤 #

1. 生成目标日期范围内的所有月份

用递归CTE生成从@StartDate到@EndDate之间每个月份的第一天，确保不会漏掉任何一个月份：

WITH DateRange AS (
    SELECT DATEFROMPARTS(YEAR(@StartDate), MONTH(@StartDate), 1) AS MonthStart
    UNION ALL
    SELECT DATEADD(MONTH, 1, MonthStart)
    FROM DateRange
    WHERE MonthStart < DATEFROMPARTS(YEAR(@EndDate), MONTH(@EndDate), 1)
)

2. 生成部门-月份的全量组合

将上面的月份列表和你指定的部门列表做交叉连接，确保每个部门在每个目标月份都有一条基础记录：

, DeptMonthCombination AS (
    SELECT 
        d.DEPARTMENTNUMBER,
        DATENAME(Month, dr.MonthStart) + '-' + DATENAME(Year, dr.MonthStart) AS MONTHYEAR,
        dr.MonthStart
    FROM DateRange dr
    CROSS JOIN Departments d
    WHERE d.DEPARTMENTNUMBER IN (@DEPTNO)
)

3. 左联培训记录统计数据

把全量的部门-月份组合左联到你的培训记录统计结果，用ISNULL将无记录月份的计数转为0：

SELECT 
    ISNULL(trStats.NUMBEROFRECORDS, 0) AS NUMBEROFRECORDS,
    dmc.DEPARTMENTNUMBER,
    dmc.MONTHYEAR
FROM DeptMonthCombination dmc
LEFT JOIN (
    -- 你的原统计逻辑作为子查询
    SELECT 
        COUNT(TRAININGRECORDID) AS NUMBEROFRECORDS,
        TD.DEPARTMENTNUMBER,
        DATENAME(Month, TR.EXPIRY) + '-' + DATENAME(Year, TR.EXPIRY) AS MONTHYEAR
    FROM Training_Records TR
    JOIN Departments TD ON TR.DEPARTMENTID = TD.DEPARTMENTID
    WHERE TR.EXPIRY IS NOT NULL 
      AND TD.DEPARTMENTNUMBER IN (@DEPTNO) 
      AND TR.EXPIRY BETWEEN @StartDate AND @EndDate
    GROUP BY TD.DEPARTMENTNUMBER, DATENAME(Year, TR.EXPIRY), DATENAME(Month, TR.EXPIRY)
) trStats ON dmc.DEPARTMENTNUMBER = trStats.DEPARTMENTNUMBER 
         AND dmc.MONTHYEAR = trStats.MONTHYEAR
ORDER BY dmc.DEPARTMENTNUMBER, dmc.MonthStart

额外说明 #

• 递归CTEDateRange是保证月份完整性的核心，哪怕某个月份没有任何到期记录也会被生成
• 排序时用MonthStart而非MONTHYEAR字符串，避免跨年时出现排序错误（比如"December-2023"不会排在"January-2024"前面）
• 原查询里的COUNT(ISNULL(TRAININGRECORDID, 0))可以简化为COUNT(TRAININGRECORDID)，因为TRAININGRECORDID作为主键不会为NULL

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