Create missing months dynamically from group on da

问题:

This question already has an answer here:

• SQL select, pad with chronological missing months 3 answers

In the following SQL i'm trying to insert rows to fill in the missing months in the results. The solution is very close thanks to post SQL select, pad with chronological missing months

But yet this code runs gr8 but still have missing months, issue is how to join/union the temp 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)

回答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)

回答2:

I think you need to reconstruct the query as 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 condition, I added t.ExpiryDate IS NULL, because this will be null for the missing month.