Sql-server – Average of last 4 runs of a product on SQL Server 2008

aggregatesql serversql-server-2008

I'm looking to do the exact same moving average from this post: Average of last 4 runs of a product

but now on SQL Server 2008, which doesn't support the extended window aggregate functions.

Is there an alternative I can look into using to create the same solution in SQL Server 2008?

Best Answer

with cte as 
( select ID, cost, ddate 
       , row_number() over (partition by ID order by ddate) as rn 
    from table 
)
select cte1.ID, cte1.ddate, avg(cte2.cost) as avg, count(cte2.cost) as count
from cte as cte1
join cte as cte2
      on cte2.ID  = cte1.ID  
     and cte2.rn >= cte1.rn 
     and cte2.rn <= cte1.rn + 3
group by cte1.ID, cte1.ddate 
order by cte1.ID, cte1.ddate
Related Question