T-SQL - Rolling 12 Month Average

Question

I would like to return rolling 12 month averages for each month in a resulting dataset but am not sure how I can do this.

I thought the following script would work:

DECLARE @StartDate as datetime
DECLARE @EndDate as datetime
SET @StartDate = '01/04/2011'
SET @EndDate = getdate()

select x.FinYear, x.FinMonth, x.MonthText, avg(TimeSeconds) [AverageTimeSeconds]
from times x
where (x.TimeOfCall >= @StartDate and x.TimeOfCall < @EndDate)
group by x.FinYear, x.FinMonth, x.MonthText
order by x.FinYear, x.FinMonth

but it only returns the monthly averages, how do I get the 12 month average leading up to each of the months between the start and end date.

The resulting dataset I am looking for is as follows:

Fin Year    Fin Month   Month Text  Avg Time Seconds    R12M Avg Seconds
2015/16 01  Apr 100 101
2015/16 02  May 95  98
2015/16 03  Jun 103 100
2015/16 04  Jul 110 100
2015/16 05  Aug 100 100
2015/16 06  Sep 90  97
2015/16 07  Oct 93  97
2015/16 08  Nov 98  100
2015/16 09  Dec 80  98
2015/16 10  Jan 88  98
2015/16 11  Feb 100 98
2016/17 12  Mar 115 100
2016/17 01  Apr 105 100
2016/17 02  May 98  100
2016/17 03  Jun 95  98
2016/17 04  Jul 102 98
2016/17 05  Aug 109 99
2016/17 06  Sep 104 100
2016/17 07  Oct 98  98
2016/17 08  Nov 99  97
2016/17 09  Dec 90  97

the rolling 12 month average is not an average of the monthly averages but an average of the 12 months leading up to the month in question. So January 2017 would be the average of 01 February 2016 - 31 January 2017 and October 2016 would be 01 November 2015 to 31 October 2016.

I hope you can help :-) .

please show your expected result,actual result set and DDL to repro the issue — TheGameiswar
tag the version of SQLServer you are using as well.look here for example:spaghettidba.com/2015/04/24/… — TheGameiswar
If you're on SQL 2012+ this is what you need: simple-talk.com/sql/t-sql-programming/… — RoundFour
@TheGameiswar I have amended the query and the expected result set is there, the actual result set is the same without the last column R12M Avg. I'm using 2008 r2 but am unable to add the tag as I need a higher reputation. — msinghm

Gordon Linoff Gordon Linoff · Accepted Answer · 2017-02-22T11:57:26

If you have data for every month, then the following calculates the average over the preceding 12 months (note this is the overall average, not the average of the monthly averages):

select x.FinYear, x.FinMonth, x.MonthText, avg(TimeSeconds)as [AverageTimeSeconds],
       (sum(sum(TimeSeconds)) over (order by x.FinYear, x.FinMonth rows between 11 preceding and current row) /
        sum(count(*)) over (order by x.FinYear, x.FinMonth rows between 11 preceding and current row)
       ) as avg_12month
from times x
where x.TimeOfCall >= @StartDate and x.TimeOfCall < @EndDate
group by x.FinYear, x.FinMonth, x.MonthText
order by x.FinYear, x.FinMonth;

Note: The where clause affects the 12-month look-back period. In other worse, the look-back will not include months before this period.

T-SQL - Rolling 12 Month Average

1 Answers