如何包含没有数据的月份？

一尘不染

如何包含没有数据的月份？

sql

我正在尝试创建一个报告，显示在所选日期范围内将有多少培训记录到期，但是当我运行报告时，它排除了没有培训记录过期的月份。我已经尝试了我看到的各种解决方案，但我无法让它们中的任何一个在我的情况下工作。

这是我的查询：

SELECT COUNT(ISNULL(TRAININGRECORDID, 0)) AS NUMBEROFRECORDS
,DEPARTMENTNUMBER
,DATENAME( Month, EXPIRY ) + '-' + DATENAME( Year, 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)
ORDER BY TD.DEPARTMENTNUMBER, [MONTHYEAR]

此查询的结果示例如下所示：

NUMBEROFRECORDS DEPARTMENTNUMBER MONTHYEAR
1                21              April-2023
4                23              June-2023
1                83              August-2023

我在以 MONTHYEAR 作为列的矩阵中显示此查询的结果。在上面的示例中，报告将显示 2023 年 4 月、6 月和 8 月，但会跳过 2023 年 5 月、7 月这几个月，因为在这些月份没有记录过期，但尽管如此，我仍然希望它们显示在我的报告中/返回我的查询。

我尝试了在这里找到的各种解决方案，但没有一个对我有用。如果没有记录过期，我将如何包括这些月份？

阅读 77

2022-07-21

共1个答案

一尘不染

您需要首先获得所有月份，然后外部加入它们（不使用BETWEEN）。这是一个获取 4 月、5 月、6 月和 7 月的示例，然后显示了如何将其与您的表进行外部联接。

DECLARE @StartDate date = '20220405', 
        @EndDate   date = '20220708';

;WITH Months(TheMonth) AS 
(
  SELECT DATEFROMPARTS(YEAR(@StartDate), MONTH(@StartDate), 1)
  UNION ALL
  SELECT DATEADD(MONTH, 1, TheMonth) 
    FROM Months
    WHERE TheMonth < DATEFROMPARTS(YEAR(@EndDate), MONTH(@EndDate), 1)
)
SELECT TheMonth -- , COALESCE(SUM({your table}.{column}),0)
FROM Months AS m
-- LEFT OUTER JOIN  {your table}
-- ON {your table}.{date column} >= m.TheMonth
-- AND {your table}.{date column} < DATEADD(MONTH, 1, m.TheMonth);

Output:

TheMonth
2022-04-01
2022-05-01
2022-06-01
2022-07-01

示例db<>fiddle

如果您的范围可以持续超过 100 个月，您需要添加：

OPTION (MAXRECURSION 0);

2022-07-21