reporting services - Add Rows for months missing from years SQL Server 2008 R2 -

i working table stores data @ monthly level. however, many items, there isn't entries months. how add rows missing months specific year range (example: 2017 - 2020)?

here's have done far:

--create temp table retrieve months , years range looking populate rows for:

declare @months table (monthnum int) declare @i int =1  while (@i<=12) begin insert @months(monthnum) select @i set @i=@i+1 end  declare @yearsrange table (monthnum int, yearsrange int)  insert @yearsrange (monthnum, yearsrange) ( select *, year(getdate()) years @months union select *, year(getdate()) + 1 years @months union select *, year(getdate()) + 2 years @months union select *, year(getdate()) + 3 years @months )  select * @yearsrange 

table structure:

--rows red ones expect end result:

first, don't use loop. can recursive cte quickly...

declare @start date = '20170101' declare @end date = '20201201'  ;with rcte as(     select thedate = @start     union     select          dateadd(month,1,thedate)              rcte             thedate < @end )  select  * rcte full outer join --or using cross apply depending on looking yourtable on asofdate = thedate 

you didn't provide sample data didn't break out date month , year. idea here...