SQL Server 2008 Rolling Average

Question

I am trying to create a SQL statement to calculate a 6 month rolling average for my data. I am following this guide:

SQL Query for 7 Day Rolling Average in SQL Server

but the problem is I am using SQL Server 2008 and the answer here is for 2012.

select 
    x.*,
    avg(dailyusage) over(partition by productid order by productid, date rows between 6 preceding and current row) as rolling_avg
from 
    (select 
         productid, date, sum(usagecount) as dailyusage
     from tbl
     group by productid, date) x

It will be appreciated if someone can help me translate it over to 2008 terms.

Thanks

what you mean change to 2008? doesnt work ? give you an error? [SQL Fiddle](http://sqlfiddle.com/#!3/f674a/1) change it to 2008 on the top left combo and works ok. — Juan Carlos Oropeza, Aug 27 '15 at 20:20
@JuanCarlosOropeza There's no `ORDER BY` for aggregation functions in SQL Server 2008 — Lamak, Aug 27 '15 at 20:22
@JuanCarlosOropeza Then that's a problem with sqlfiddle, but it doesn't really mean that you can use it — Lamak, Aug 27 '15 at 20:27
@JuanCarlosOropeza SQL Fiddle is actually running on 2014 server using hypervisors, and the 2008 option just means that the compatibility level will be set to 100 and for some reason SQL Server uses the `order by` even though it shouldn't when compat. lvl is set to 100. — jpw, Aug 27 '15 at 20:46

score 1 · Answer 1 · edited Aug 27 '15 at 20:49

You could try to use OUTER APPLY, but it won't be as efficient:

;WITH CTE AS
(
    SELECT  *,
            ROW_NUMBER() OVER(PARTITION BY productid ORDER BY [date]) RN
    FROM (  SELECT productid, [date], SUM(usagecount) dailyusage
            FROM dbo.YourTable
            GROUP BY productid, [date]
         ) t
)
SELECT TOP 100 *
FROM CTE A
OUTER APPLY(SELECT AVG(dailyusage) rolling_avg
            FROM CTE
            WHERE productid = A.productid
            AND A.RN - RN BETWEEN 0 AND 6) B;

SQL Server 2008 Rolling Average

1 Answers1