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

Answer 1

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.