sql - Calculate Sum based on dates (Current, 7 Days,...) -

the following example of table working with:

date -------- number -------total  2012-03-28     3         158                 2012-03-29     4         168               2012-04-08     2         256             2012-04-12     1         155.98              2012-04-14     6         245.00              2012-04-20     10        156               2012-04-21     8         87       2012-04-26     3         158                 2012-04-26     4         168               2012-04-29     2         256             2012-04-30     1         155.98              2012-05-02     6         245.00              2012-05-02     10        156               2012-05-02     8         87            

i need derive table following this:

total ----- current ----7days----14days  2451.96    1225.98    1869.96    1869.96               

in case total sum(total), current -7 days todays date(05/02/2013) adds sum 05/02/2013-04/25/2013 7 days -14 todays date or -7 current. adds sum 05/02/2013-04/18/2013.

so forth. not know how create query sum 7days.

please help!.

you can use aggregate function case expression result:

select    sum(total) total,   sum(case when date >= dateadd(d, -7, getdate()) total end) [current],   sum(case when date >= dateadd(d, -14, getdate()) total end) [7days],   sum(case when date >= dateadd(d, -21, getdate()) total end) [14days] yt; 

see sql fiddle demo

if have more date ranges, add more sum(case...) expressions.