Using {Rows Preceding} for moving average sql

Question

In the following code I'm trying to compute moving average of each month compared to the last 6 months,However SQL is excluding the rows which are null in the denominator for average.

For Example:-

Lets say avg(count(*)) for Sep is 4 and values from March till august are 0.In my code,SQL is taking 4/1 but I need it to include the previous months too even if they have no values (4/6).

select [Name],[Report Month]
,avg(count(*))  over (partition by [Name]
                              order by [Report Month]
                              ROWS 5 PRECEDING)
FROM dbo.Sample
group by [Name],[Report Month]

As per Tab's suggestion.I tried creating a month table to be left joined with dbo.sample,I'm unfamiliar with creating a month table,Hence I used a online resource,Following is the code.(I'm using SQLServer2014,It said in the resource,the code is for 2000)

CREATE TABLE CalendarMonths (
  date DATETIME,
  PRIMARY KEY (date)
)

DECLARE
  @basedate DATETIME,
  @offset   INT
SELECT
  @basedate = '01 Jan 2000',
  @offset = 1

WHILE (@offset < 2048)
BEGIN
  INSERT INTO CalendarMonths SELECT DATEADD(MONTH, @offset, date) FROM CalendarMonths
  SELECT @offset = @offset + @offset
END

You help is much appreciated!!..Thank you in Advance.

Best,

I tried creating a month table but it shows 0 rows affected,Can you please share your query? — Give_me_data
I don't have anything prepared to share. Edit your post and add your attempt with the month table so we can debug your effort. — Tab Alleman
Thank you Tab,I have edited the post with the query I was trying to execute — Give_me_data

Tab Alleman Tab Alleman · Accepted Answer · 2015-09-16T13:28:10

You got 0 rows affected because you did INSERT...SELECT from an empty table.

It should work if you replace this:

 INSERT INTO CalendarMonths SELECT DATEADD(MONTH, @offset, date) FROM CalendarMonths
 SELECT @offset = @offset + @offset

with this:

INSERT INTO CalendarMonths SELECT DATEADD(MONTH, @offset, @basedate)
SET @offset = @offset + 1

Then join to this month table on the month and year only, like this:

select [Name],cm.[date] AS ReportMonth
,avg(count(*))  over (partition by [Name]
                              order by cm.[date]
                              ROWS 5 PRECEDING)
FROM dbo.Sample s
RIGHT OUTER JOIN CalendarMonths cm
  ON MONTH(s.[Report Month])=MONTH(cm.[date])
  AND YEAR(s.[Report Month])=YEAR(cm.[date])

group by [Name],cm.[date]

Using {Rows Preceding} for moving average sql

1 Answers