0

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
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
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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