Rolling SUM of last N rows - grouping question

[TheDrider]

Assuming I have a table with months and totals, I need to return the last 6 months of data, with each month being a total of itself and the preceding 5 months.

Here's sample source data (you can assume the MONTH to be datetime fields):

MONTH QY
7-2008 87
6-2008 33
5-2008 12
4-2008 76
3-2008 14
....

Returning the latest 6 months is easily done with a where, but I'm having difficulties coming up with a derived table for the sums. I'd need July's output number to be the total of July plus the previous 5 months. June would be the same: June plus it's previous 5 months.

I can do it easy enough with a loop and a temp table, but I'd prefer a cleaner solution if one is possible.

Any pointers?

 

[Qik3Coder]

Are you going to have a column for this months total, and a separate column for a rolling total? Otherwise you are going to have weird sum issues, trying to calculate every x months.

you are either going to wind up with one big ugly query or else a lot of little steps. I used to like to do it all in one big query, but have found better performance out of explicitly doing each step independently. I believe the db can handle a lot of little things faster than one big ugly.

-Sometimes the answer to your question is the hack that works

 

[TheDrider]

A big, ugly query is what I was afraid of. If it's a simple self join that I missed I'd be ok with it. It's already working well enough with the loop, it would just be nice to have an all-encapsulated *simple-to-understand* query. I'll keep the loop unless someone comes up with something clever.

Thanks again.

 

[philhege]

This example works with SQL 2005, using the AdventureWorks database:

select SUM(orderqty*unitprice) as monthtotal,
month(duedate) as purchase_month,
year(duedate) as purchase_year,
ROW_NUMBER() OVER(order by year(duedate), month(duedate)) AS RowNumber
into #temp
FROM purchasing.purchaseorderdetail
GROUP by month(duedate), year(duedate)

select a.monthtotal, a.purchase_month, a.purchase_year,a.rownumber,
rolling_total = coalesce((select sum(monthtotal) from
#temp b where (b.rownumber < a.rownumber and b.rownumber > a.rownumber-6)),0)+monthtotal
from #temp a
drop table #temp

I'd like to thank Tony Rogerson for his ideas in this thread. There are a number of other approaches there, but I like this one the best. The example I wrote takes data from 8845 rows, and the performance is what I'd call satisfactory.

HTH,

Phil H.
Some Bank
-----------
Time's fun when you're having flies.

 

[RyanEK]

Hi,

This should work. Does it work with your data?

select t1.*,
(select sum(qy) from [Table] t2 
 where t2.date between dateadd(m,-5,t1.date) and t1.date) as RollSum
from [Table] as t1
order by date desc

Ry