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?


1 Answer 1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.