I am trying to create a report that shows how many training records will expire within a chosen date range however when I run the report it excludes months that have no training records going out of date. I have tried various solutions I've seen posted but I haven't been able to get any of them to work in my case.

This is my query:

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]

An example of results from this query looks like this:

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

I am displaying the results of this query in a matrix with MONTHYEAR as the columns. In the example above the report will display April, June and August 2023 but will skip over the months May, July 2023 because there are no records going out of date in those months but despite that I still want them displayed in my report/returned in my query.

I've tried various solutions I've found on here but none of them have worked for me. How would I go about including these months with no records going out of date?

CodePudding user response：

You need to first get all of the months, and then outer join to them (not using BETWEEN). Here is an example that gets April, May, June, and July, and then shows how you would outer join that against your table.

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:

• Example db<>fiddle

If your range could last more than 100 months, you'll need to add:

OPTION (MAXRECURSION 0);