Summing Last 4 Weeks Every Week

[lazyme]

It sound simple enough but I couldn't figure it out. How do you sum numbers for a 4 week period every week?

EX:

Week Qty
----- ----
1 10
2 20
3 30
4 40
5 50
6 60

Result:

Week Qty
----- ----
4 100
5 140
6 180

Any help would be appreciated

 

[pbaldy]

You could do it with a DSum or sub query that summed the current week through the current week less 3. Search on running sum and you should find the basics. If the week number resets at the beginning of the year, that will complicate matters.

 

[lazyme]

How would you write the subquery so that it goes through every week and sums the last 4 week? I can only figure out how to do one specific week.

 

[pbaldy]

Something like

WHERE Alias.Week Between (TableName.Week - 3) And TableName.Week

 

[lazyme]

Sorry, I am completely new to SQL, I tried reading DSum and Subquery but still couldn't figure it out.

I tried:
SELECT T1.Week, Sum(DSum("[Qty]","T1","[Week] = [Week] and [Week] > [Week]-3")) AS L4W
FROM T1
GROUP BY T1.Week;

This only sums up everything for every week.

I have no idea how to write the subquery.....

Any more guidance?

 

[pbaldy]

Try

SELECT T1.Week, T1.Qty, (SELECT sum(Alias.Qty)
FROM T1 AS Alias WHERE Alias.Week Between T1.Week - 3 And T1.Week) AS RunSum
FROM T1
ORDER BY T1.Week

 

[lazyme]

Wow, great, that works. It will take me some time to digest it though.

Thanks for your help!