Dynamically create missing months from a group by date

Question

In the following SQL, I am trying to insert rows to fill in the missing months in the results. Solution is very close thanks to SQL select, pad message with chronological missing months

But while this code runs gr8, but still not enough months, the problem is how to join / merge the temporary table

DECLARE @StartDate DATETIME = dateadd(m,-12,getdate()), @EndDate DATETIME = getdate(), @DATE DATETIME

DECLARE @TEMP AS TABLE (MeterReadDate datetime)

SET @DATE = @StartDate

WHILE @DATE <= @EndDate
BEGIN
     INSERT INTO @TEMP VALUES ( @DATE)
    SET @DATE = DATEADD(MONTH,1,@DATE)
END



SELECT convert(char(7), t.MeterReadDate, 121),count(*)

  FROM @TEMP m left join
     [PremiseMeterReadProviders] t
     on convert(char(7), t.MeterReadDate, 121) = convert(char(7), m.MeterReadDate, 121)

  where (t.MeterReadDate > dateadd(m,-12,getdate()))
  group by  convert(char(7), t.MeterReadDate, 121)
  order by  convert(char(7), t.MeterReadDate, 121)

Answer 1

Try something like this ....

;WITH Months AS 
(
 SELECT TOP 12 
   CONVERT(CHAR(7),
       DATEADD(MONTH 
               , - ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
               , GETDATE()
               )
           ,121) MonthNo
 FROM master..spt_values
 )
SELECT convert(char(7), t.MeterReadDate, 121),count(*)
FROM Months m
LEFT JOIN  [PremiseMeterReadProviders] t
      ON convert(char(7), t.MeterReadDate, 121) = m.MonthNo
     AND (t.MeterReadDate > dateadd(m,-12,getdate()))
group by  convert(char(7), t.MeterReadDate, 121)
order by  convert(char(7), t.MeterReadDate, 121)

Answer 2

I think you need to restore the request below

DECLARE @StartDate DATETIME = dateadd(m,-12,getdate()), @EndDate DATETIME = getdate(), @DATE DATETIME

DECLARE @TEMP AS TABLE (MeterReadDate datetime)

SET @DATE = @StartDate

WHILE @DATE <= @EndDate
BEGIN
     INSERT INTO @TEMP VALUES ( @DATE)
    SET @DATE = DATEADD(MONTH,1,@DATE)
END



SELECT convert(char(7), m.MeterReadDate, 121),count(*)

  FROM @TEMP m left join
     [Announcement] t
     on convert(char(7), t.ExpiryDate, 121) = convert(char(7), m.MeterReadDate, 121)

  WHERE (t.ExpiryDate IS NULL OR t.ExpiryDate > dateadd(m,-12,getdate()))
  group by  convert(char(7), m.MeterReadDate, 121)
  order by  convert(char(7), m.MeterReadDate, 121)

In the where clause, I added t.ExpiryDate IS NULL, because it will be null for the missing month.