Filling in data as zero which is not existing in database SSRS

Question

SCHOOL YEAR	JAN	FEB	MAR	APR	MAY	JUN	JUL	AUG
2022-2023	5	9	4	6	3	2	1	8
2023-2024	4	3	7	6	4	1	1	2
2024-2025	3	11	5	4	4	2	7	5

I have used a matrix in SSRS with schoolyear as rows, month as data and count(student) as columns.

Referred to SQL Server 2019 and 2022 SSRS

I have a select query as:

Select schoolname, datename(month, date_01) as month, student name, schoolyear
from table 1 q
join
table 2
on
q.(column)=w.(column)
where schoolyear in ('2022-2023','2023-2024','2024-2025')
group by .......
order by .....

date_01 was in yyyy/mm/dd which I converted into months.

Schoolname I have used as a parameter which as school is selected based on that my data changes.

The numbers shown are a count of student name in different months as they were registered in these months.

Now, ABC school has only data from Jan to Aug and no student was registered after that. It changes for all schools basically so no fixed pattern.

SCHOOL YEAR	JAN	FEB	MAR	APR	MAY	JUN	JUL	AUG	SEPT	OCT	NOV	DEC
2022-2023	5	9	4	6	3	2	1	8	0	0	0	0
2023-2024	4	3	7	6	4	1	1	2	0	0	0	0
2024-2025	3	11	5	4	4	2	7	5	0	0	0	0

I couldn't recall how to do this one. For schools having data in all months are okay but schools having only data for a few months is hard to show zero for other months as there is no data existing for them. Is there anything I could do in SQL Server script or SSRS?

Answer 1

An acceptable way would be to create a calendar table to use as the base table and left join your data accordingly. The snippet below achieves the same thing.

DECLARE @StartYear INT = 2022
DECLARE @EndYear INT = 2024 
DECLARE @MonthsOut INT = (@EndYear-@StartYear+1) * 12

;WITH R1(N) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
R2(N) AS (SELECT 1 FROM R1 a, R1 b),
R3(N) AS (SELECT 1 FROM R2 a, R2 b), 
Tally(Number) AS (SELECT  ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM R3)
, 
Calendar AS
(
    SELECT  
        CASE WHEN (Number % 12) = 0 THEN 12 ELSE (Number % 12) END AS [Month],
        ROW_NUMBER() OVER (PARTITION BY Number % 12 ORDER BY Number) + @StartYear -1 AS StartYear,
        ROW_NUMBER() OVER (PARTITION BY Number % 12 ORDER BY Number) + @StartYear  AS EndYear,
        Number AS [CalendarOrder] 
    FROM 
        Tally
    WHERE
        Number <= @MonthsOut
)
Select schoolname, datename(month, date_01) as month, student name, schoolyear
from 
Calendar cal
left join
table 1 q on datepart(month, date_01) = cal.[Month] and datepart(year, date_01)  cal.[startyear]
left join
table 2
on
q.(column)=w.(column)
where cal.schoolyear in ('2022-2023','2023-2024','2024-2025')
group by .......
order by .....,cal.CalendarOrder