Ageing from system date by number of days

Can anyone help with how I write a script that takes a balance and then age
it by its transaction dates into 30, 60 90 days etc?
My script is as follows and the "AS2 on the 2nd line is coming out as
incorrect syntax:
SELECT
GoodsValueInAccountCurrency, TransactionDate,
DATEDIFF(MONTH, TransactionDate, GETDATE()) AS no_of_days_since,
SUM(GoodsValueInAccountCurrency) AS THIRTY WHERE (no_of_days_since BETWEEN 1
AND 30) AND
SUM(GoodsValueInAccountCurrency) AS SIXTY WHERE (no_of_days_since between 31
and 60) AND
SUM(GoodsValueInAccountCurrency) AS NINETY WHERE (no_of_days_since between
61 and 90)AND
SUM(GoodsValueInAccountCurrency) AS MORE WHERE (no_of_days_since > 90)
FROM PLPostedSupplierTran
"WendyUK" wrote:

> Can anyone help with how I write a script that takes a balance and then age
> it by its transaction dates into 30, 60 90 days etc?
|||Wendy,
A couple of things:
1 - DATEDIFF (MONTH... ) does not give you what you want since February 28
and March 1 are one month apart. Use DAY instead of MONTH.
2 - WHERE appears after the FROM and only 1 per query. For your columns,
read about the CASE statement. You will need something like the following
untested code:
SELECT GoodsValueInAccountCurrency, TransactionDate,
SUM (CASE ((DATEDIFF(DAY,TransactionDate, GETDATE()) - 1) / 30)
WHEN(0) THEN GoodsValueInAccountCurrency
ELSE 0
END ) AS THIRTY,
SUM (CASE ((DATEDIFF(DAY,TransactionDate, GETDATE()) - 1) / 30)
WHEN(1) THEN GoodsValueInAccountCurrency
ELSE 0
END ) AS SIXTY,
. . . etc
RLF
"WendyUK" <WendyUK@.discussions.microsoft.com> wrote in message
news:681A829D-F604-41D0-A485-B9B92132180C@.microsoft.com...[vbcol=seagreen]
> My script is as follows and the "AS2 on the 2nd line is coming out as
> incorrect syntax:
> SELECT
> GoodsValueInAccountCurrency, TransactionDate,
> DATEDIFF(MONTH, TransactionDate, GETDATE()) AS no_of_days_since,
> SUM(GoodsValueInAccountCurrency) AS THIRTY WHERE (no_of_days_since BETWEEN
> 1
> AND 30) AND
> SUM(GoodsValueInAccountCurrency) AS SIXTY WHERE (no_of_days_since between
> 31
> and 60) AND
> SUM(GoodsValueInAccountCurrency) AS NINETY WHERE (no_of_days_since between
> 61 and 90)AND
> SUM(GoodsValueInAccountCurrency) AS MORE WHERE (no_of_days_since > 90)
>
> FROM PLPostedSupplierTran
>
> "WendyUK" wrote: