Rolling sum question

[none]

I am trying to write a query/report where I can come up with a rolling
sum of the past five days for each line item that I have.

For example:

Transaction Date Amount
15-Jan-06 ($5.50)
17-Jan-06 ($2.00)
18-Jan-06 ($3.75)
23-Jan-06 ($2.00)
26-Jan-06 ($2.00)
27-Jan-06 ($1.75)
29-Jan-06 ($3.50)
30-Jan-06 ($5.25)
31-Jan-06 ($1.75)
01-Feb-06 ($2.00)
03-Feb-06 ($1.75)
05-Feb-06 ($1.75)
09-Feb-06 ($3.75)
10-Feb-06 ($5.50)
11-Feb-06 ($10.50)
12-Feb-06 ($1.75)
15-Feb-06 ($1.75)
20-Feb-06 ($1.75)
22-Feb-06 ($2.00)
24-Feb-06 ($1.75)

So that I would have a column where, for example, for 30-Jan-06, I
would have the sum of the values from 26-Jan to 30-Jan.

Any help would be greatly appreciated!

[Bob Quintal]

"none" <rw*******@yahoo.com> wrote in
news:11**********************@f6g2000cwb.googlegro ups.com:

I am trying to write a query/report where I can come up with a
rolling sum of the past five days for each line item that I
have.

For example:

Transaction Date Amount
15-Jan-06 ($5.50)
17-Jan-06 ($2.00)
18-Jan-06 ($3.75)
23-Jan-06 ($2.00)
26-Jan-06 ($2.00)
27-Jan-06 ($1.75)
29-Jan-06 ($3.50)
30-Jan-06 ($5.25)
31-Jan-06 ($1.75)
01-Feb-06 ($2.00)
03-Feb-06 ($1.75)
05-Feb-06 ($1.75)
09-Feb-06 ($3.75)
10-Feb-06 ($5.50)
11-Feb-06 ($10.50)
12-Feb-06 ($1.75)
15-Feb-06 ($1.75)
20-Feb-06 ($1.75)
22-Feb-06 ($2.00)
24-Feb-06 ($1.75)

So that I would have a column where, for example, for
30-Jan-06, I would have the sum of the values from 26-Jan to
30-Jan.

Any help would be greatly appreciated!

Use a subquery that gets the sum,

SELECT [Transaction Date], Amount, (SELECT Sum(Amount) from
Table as Sub Where Sub.[Transaction Date] between Main.
[Transaction date]-5 And Main.[Transaction Date]) as Main;

--
Bob Quintal

PA is y I've altered my email address.

[none]

Sorry for the ignorance ... I've never used subqueries before.

So, if I have a query that reads as:

SELECT TransactionHistory.[Transaction Date],
Sum(TransactionHistory.Amount) AS SumOfAmount
FROM TransactionHistory
GROUP BY TransactionHistory.[Transaction Date];

where would I put the subquery code below?

Thanks!

Bob Quintal wrote:

"none" <rw*******@yahoo.com> wrote in
news:11**********************@f6g2000cwb.googlegro ups.com:

I am trying to write a query/report where I can come up with a
rolling sum of the past five days for each line item that I
have.

For example:

Transaction Date Amount
15-Jan-06 ($5.50)
17-Jan-06 ($2.00)
18-Jan-06 ($3.75)
23-Jan-06 ($2.00)
26-Jan-06 ($2.00)
27-Jan-06 ($1.75)
29-Jan-06 ($3.50)
30-Jan-06 ($5.25)
31-Jan-06 ($1.75)
01-Feb-06 ($2.00)
03-Feb-06 ($1.75)
05-Feb-06 ($1.75)
09-Feb-06 ($3.75)
10-Feb-06 ($5.50)
11-Feb-06 ($10.50)
12-Feb-06 ($1.75)
15-Feb-06 ($1.75)
20-Feb-06 ($1.75)
22-Feb-06 ($2.00)
24-Feb-06 ($1.75)

So that I would have a column where, for example, for
30-Jan-06, I would have the sum of the values from 26-Jan to
30-Jan.

Any help would be greatly appreciated!

Use a subquery that gets the sum,

SELECT [Transaction Date], Amount, (SELECT Sum(Amount) from
Table as Sub Where Sub.[Transaction Date] between Main.
[Transaction date]-5 And Main.[Transaction Date]) as Main;

--
Bob Quintal

PA is y I've altered my email address.

[Bob Quintal]

"none" <rw*******@yahoo.com> wrote in
news:11**********************@i39g2000cwa.googlegr oups.com:

Sorry for the ignorance ... I've never used subqueries before.

So, if I have a query that reads as:

SELECT TransactionHistory.[Transaction Date],
Sum(TransactionHistory.Amount) AS SumOfAmount
FROM TransactionHistory
GROUP BY TransactionHistory.[Transaction Date];

where would I put the subquery code below?

Thanks!
SELECT main.[Transaction Date],
Sum(main.Amount) as SumofAmount,
(SELECT Sum(sub.Amount)
from TransactionHistory alias Sub
Where Sub.[Transaction Date]
between Main.[Transaction date]-5
And Main.[Transaction Date])
FROM TransactionHistory alias Main
Group by main.[transaction date];

Bob Quintal wrote:

"none" <rw*******@yahoo.com> wrote in
news:11**********************@f6g2000cwb.googlegro ups.com:

> I am trying to write a query/report where I can come up with a > rolling sum of the past five days for each line item that I
> have.
>
> For example:
>
> Transaction Date Amount
> 15-Jan-06 ($5.50)
> 17-Jan-06 ($2.00)
> 18-Jan-06 ($3.75)
> 23-Jan-06 ($2.00)
> 26-Jan-06 ($2.00)
> 27-Jan-06 ($1.75)
> 29-Jan-06 ($3.50)
> 30-Jan-06 ($5.25)
> 31-Jan-06 ($1.75)
> 01-Feb-06 ($2.00)
> 03-Feb-06 ($1.75)
> 05-Feb-06 ($1.75)
> 09-Feb-06 ($3.75)
> 10-Feb-06 ($5.50)
> 11-Feb-06 ($10.50)
> 12-Feb-06 ($1.75)
> 15-Feb-06 ($1.75)
> 20-Feb-06 ($1.75)
> 22-Feb-06 ($2.00)
> 24-Feb-06 ($1.75)
>
> So that I would have a column where, for example, for
> 30-Jan-06, I would have the sum of the values from 26-Jan to > 30-Jan.
>
> Any help would be greatly appreciated!
>

Use a subquery that gets the sum,

SELECT [Transaction Date], Amount, (SELECT Sum(Amount) from
Table as Sub Where Sub.[Transaction Date] between Main.
[Transaction date]-5 And Main.[Transaction Date]) as Main;

--
Bob Quintal

PA is y I've altered my email address.

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com