SQL Rollup last 4 weeks total

Question

I have a table which I want to get the previous four weeks Order total in a query. But I want to return it with a SELECT (A total of the row's previous 4 weeks Order1 column - if they exist)

PurchasingID Order1              Date         FourWeekTotal
------------ ------------------- -------      ---------------
1            1.00                2013-04-21   14.00
2            2.00                2013-04-14   12.00
3            3.00                2013-04-07   9.00
4            4.00                2013-03-31   5.00
5            5.00                2013-03-24   0.00

from where you get 14.00 value – Justin Mar 27 '13 at 08:22 — Justin, Mar 27 '13 at 08:22

Moho · Accepted Answer · 2013-03-27T06:00:19.083

2

My understanding is for each record in your table, you want to see the sum of Order1 for itself and each record that has a Date value within four weeks prior to the primary record. Here you go:

create table MysteryTable
(
    PurchasingId int not null primary key identity(1,1),
    Order1 money not null,
    [Date] date not null
)

insert MysteryTable( Order1, [Date] ) values ( 1.00, '2013-04-21' )
insert MysteryTable( Order1, [Date] ) values ( 2.00, '2013-04-14' )
insert MysteryTable( Order1, [Date] ) values ( 3.00, '2013-04-07' )
insert MysteryTable( Order1, [Date] ) values ( 4.00, '2013-03-31' )
insert MysteryTable( Order1, [Date] ) values ( 5.00, '2013-03-24' )

select
    t1.PurchasingId
    , t1.Order1
    , t1.Date
    , SUM( ISNULL( t2.Order1, 0 ) ) FourWeekTotal
from
    MysteryTable t1
    left outer join MysteryTable t2
     on DATEADD( ww, -4, t1.Date ) <= t2.Date and t1.Date > t2.Date
group by
    t1.PurchasingId
    , t1.Order1
    , t1.Date
order by
    t1.Date desc

Explanation:

Join the table on itself, t1 representing the records to return, t2 to be the records to aggregate. Join based on t1's Date minus four weeks being less than or equal to t2's Date and t1's Date being greater than t2's Date. Then group the records by the t1 fields and sum t2.Order1. Left outer join is to account for the one record that will not have any preceding data.

edited Mar 27 '13 at 06:00

answered Mar 27 '13 at 05:14

Moho

15,457
1
30
31

thanks, but I can't even find a sum that works with these results from your query - and it only returns 7 of my 11 purchasing rows. I need them all returned with the previous 4 weeks Order total (not including the current). I've simplified the data above if that helps – Colbs Mar 27 '13 at 05:41
oops my bad, my on clause is incorrect. too sleepy :P updating now. BTW, based on your sample data your four week total does not include the current record's Order1 value, is that correct? – Moho Mar 27 '13 at 05:57
programmers don't sleep! that's correct, the current Order1 isn't included – Colbs Mar 27 '13 at 06:00
Oh sorry, just noticed you updated the query. I was waiting but I will try again this evening. ty – Colbs Mar 28 '13 at 14:58
Sorry to revive this question, but I have a new requirement that I'm really struggling with. I have a 'Location' column now so I want to make the query smart enough to group these 4 week sums by Location. Right now, they are accumulating values from both Locations (since the dates can overlap) Any Ideas / Additions ? really appreciate any help – Colbs Apr 24 '13 at 04:39

score 0 · Answer 2 · answered Mar 27 '13 at 05:09

0

Try this...

Declare @LBDate date
SET @LBDate = DATEADD(d,-28,getdate())

Now write ur select query...

Select * from Orders where Date between @LBDate and Getdate()

You can also use your required date instead to current date..

answered Mar 27 '13 at 05:09

Shahid Iqbal

2,095
8
31
51

this returns the first row – Colbs Mar 27 '13 at 05:49

SQL Rollup last 4 weeks total

2 Answers2