Query Assistance Requested.. Summing Data

Howdy gang,
Quick query question for the query gurus
I want to crete a query that SUMs a field for the last 7 records in its
own field.
Here is my @.example table
Date Money
--
1/1/01 $100
1/2/01 $200
1/3/01 $300
1/4/01 $100
1/5/01 $200
1/6/01 $200
1/7/01 $500
1/8/01 $200
1/9/01 $400
1/10/01 $100
1/11/01 $200
1/12/01 $100
1/13/01 $100
1/14/01 $200
1/15/01 $700
1/16/01 $100
1/17/01 $400
**This is what I am running**
Select Date, Money,
Case When (Select DATENAME(dw,Date)) = 'Monday'
Then (Select SUM(Money) from @.Example
Where Date between DateAdd(Day,-7,Date) and Date)
Else NULL End as WeekSalesTotal
>From @.Example
What I am trying to accomplish:
When it is Monday, I want to add the previous 7 days Money together
into a new field we will call WeekSalesTotal. All records for days
other than Monday the WeekSalesTotal field would be NULL. My table
should look like following if done correctly.
Date Money WeekSalesTotal
--
1/1/01 $100 $100
1/2/01 $200 NULL
1/3/01 $300 NULL
1/4/01 $100 NULL
1/5/01 $200 NULL
1/6/01 $200 NULL
1/7/01 $500 NULL
1/8/01 $200 $1500
1/9/01 $400 NULL
1/10/01 $100 NULL
1/11/01 $200 NULL
1/12/01 $100 NULL
1/13/01 $100 NULL
1/14/01 $200 NULL
1/15/01 $700 $1800
1/16/01 $100 NULL
1/17/01 $400 NULL
I have been staring at this issue for a little bit and was hoping fresh
eyes on it would help.
Thank YouOn 2 Nov 2005 09:37:44 -0800, EvilReportingGenius wrote:
(snip)[vbcol=seagreen]
>**This is what I am running**
>Select Date, Money,
> Case When (Select DATENAME(dw,Date)) = 'Monday'
> Then (Select SUM(Money) from @.Example
> Where Date between DateAdd(Day,-7,Date) and Date)
> Else NULL End as WeekSalesTotal
Hi EvilReportingGenius,
Try what happens if you change this to:
Select Date, Money,
Case When (Select DATENAME(dw,Date)) = 'Monday'
Then (Select SUM(Money) from @.Example AS b
Where b.Date between DateAdd(Day,-7,a.Date) and a.Date)
Else NULL End as WeekSalesTotal
From @.Example AS a
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)