Ageing by Date to 30, 60,90 etc

I am trying to age transactions into 30 days, 60 days etc and have put
together the following script but is giving a syntax error on "AS" on the 2nd
SUM line, can anyone please help me.

SELECT

PLSupplierAccountID, GoodsValueInAccountCurrency, TransactionDate,
DATEDIFF(DAY, 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
GROUP by PLSupplierAccountID, TransactionDate, GoodsValueInAccountCurrency

You can have only one Where in a query, unless you are using SubQueries
(which is not the case), instead use IIf for the criteria.

Soething like:
SELECT
PLSupplierAccountID, GoodsValueInAccountCurrency, TransactionDate,
DATEDIFF(DAY, TransactionDate, GETDATE()) AS no_of_days_since,
SUM(IIf(no_of_days_since BETWEEN 1 AND 30, GoodsValueInAccountCurrency, 0))
AS THIRTY ,
SUM(IIf(no_of_days_since between 31 and 60 , GoodsValueInAccountCurrency,
0)) AS SIXTY,
SUM(IIf(no_of_days_since between 61 and 90 , GoodsValueInAccountCurrency, 0))
AS NINTY ,
SUM(IIf(no_of_days_since 90 , GoodsValueInAccountCurrency, 0))
As GreaterThenNinty

FROM PLPostedSupplierTran
GROUP by PLSupplierAccountID, TransactionDate, GoodsValueInAccountCurrency
--
Good Luck
BS"D


"WendyUK" wrote:

I am trying to age transactions into 30 days, 60 days etc and have put
together the following script but is giving a syntax error on "AS" on the 2nd
SUM line, can anyone please help me.

SELECT

PLSupplierAccountID, GoodsValueInAccountCurrency, TransactionDate,
DATEDIFF(DAY, 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
GROUP by PLSupplierAccountID, TransactionDate, GoodsValueInAccountCurrency

hi Wendy,

WendyUK wrote:
SELECT
PLSupplierAccountID, GoodsValueInAccountCurrency, TransactionDate,
DATEDIFF(DAY, 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
GROUP by PLSupplierAccountID, TransactionDate, GoodsValueInAccountCurrency
Interesting approach, but while it doesen't work, it also contains a
major mistake:

You won't get a real SUM() when using GROUP BY for the same field, e.g.

SELECT id, Sum(id) FROM table GROUP BY id.

I'm also not sure, whether the GROUP BY TransactionDate gives you the
correct results.


You like to aggregate your GoodsValue per month.

You need two queries like these:

1. Calculate passed days

SELECT *,
DATEDIFF(DAY, TransactionDate, GETDATE()) AS no_of_days_since
FROM PLPostedSupplierTran

Save this query as daysSince.

2. An query to generate your groups

SELECT PLSupplierAccountID, TransactionDate,
DSum("GoodsValueInAccountCurrency",
"daysSince",
"(no_of_days_since BETWEEN 1 AND 30) AND" &
"(PLSupplierAccountID = " & PLSupplierAccountID & ") AND " &
"(TransactionDate = " & TransactionDate & ")"
FROM PLPostedSupplierTran
GROUP BY PLSupplierAccountID, TransactionDate

Not tested, but it should show you the right direction.


mfG
-- stefan --

"Ofer Cohen" wrote:

You can have only one Where in a query, unless you are using SubQueries
(which is not the case), instead use IIf for the criteria.

Soething like:
SELECT
PLSupplierAccountID, GoodsValueInAccountCurrency, TransactionDate,
DATEDIFF(DAY, TransactionDate, GETDATE()) AS no_of_days_since,
SUM(IIf(no_of_days_since BETWEEN 1 AND 30, GoodsValueInAccountCurrency, 0))
AS THIRTY ,
SUM(IIf(no_of_days_since between 31 and 60 , GoodsValueInAccountCurrency,
0)) AS SIXTY,
SUM(IIf(no_of_days_since between 61 and 90 , GoodsValueInAccountCurrency, 0))
AS NINTY ,
SUM(IIf(no_of_days_since 90 , GoodsValueInAccountCurrency, 0))
As GreaterThenNinty

FROM PLPostedSupplierTran
GROUP by PLSupplierAccountID, TransactionDate, GoodsValueInAccountCurrency
--
Good Luck
BS"D


"WendyUK" wrote:

I am trying to age transactions into 30 days, 60 days etc and have put
together the following script but is giving a syntax error on "AS" on the 2nd
SUM line, can anyone please help me.

SELECT

PLSupplierAccountID, GoodsValueInAccountCurrency, TransactionDate,
DATEDIFF(DAY, 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
GROUP by PLSupplierAccountID, TransactionDate, GoodsValueInAccountCurrency

Thank you but this one falls over on the "between"

"Ofer Cohen" wrote:

You can have only one Where in a query, unless you are using SubQueries
(which is not the case), instead use IIf for the criteria.

Soething like:
SELECT
PLSupplierAccountID, GoodsValueInAccountCurrency, TransactionDate,
DATEDIFF(DAY, TransactionDate, GETDATE()) AS no_of_days_since,
SUM(IIf(no_of_days_since BETWEEN 1 AND 30, GoodsValueInAccountCurrency, 0))
AS THIRTY ,
SUM(IIf(no_of_days_since between 31 and 60 , GoodsValueInAccountCurrency,
0)) AS SIXTY,
SUM(IIf(no_of_days_since between 61 and 90 , GoodsValueInAccountCurrency, 0))
AS NINTY ,
SUM(IIf(no_of_days_since 90 , GoodsValueInAccountCurrency, 0))
As GreaterThenNinty

FROM PLPostedSupplierTran
GROUP by PLSupplierAccountID, TransactionDate, GoodsValueInAccountCurrency
--
Good Luck
BS"D


"WendyUK" wrote:

I am trying to age transactions into 30 days, 60 days etc and have put
together the following script but is giving a syntax error on "AS" on the 2nd
SUM line, can anyone please help me.

SELECT

PLSupplierAccountID, GoodsValueInAccountCurrency, TransactionDate,
DATEDIFF(DAY, 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
GROUP by PLSupplierAccountID, TransactionDate, GoodsValueInAccountCurrency

Thank you Stefan, this seems to work except for an error at the "FROM" is
that because there is syntax immediately before the from?

"Stefan Hoffmann" wrote:

hi Wendy,

WendyUK wrote:
SELECT
PLSupplierAccountID, GoodsValueInAccountCurrency, TransactionDate,
DATEDIFF(DAY, 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
GROUP by PLSupplierAccountID, TransactionDate, GoodsValueInAccountCurrency
Interesting approach, but while it doesen't work, it also contains a
major mistake:

You won't get a real SUM() when using GROUP BY for the same field, e.g.

SELECT id, Sum(id) FROM table GROUP BY id.

I'm also not sure, whether the GROUP BY TransactionDate gives you the
correct results.


You like to aggregate your GoodsValue per month.

You need two queries like these:

1. Calculate passed days

SELECT *,
DATEDIFF(DAY, TransactionDate, GETDATE()) AS no_of_days_since
FROM PLPostedSupplierTran

Save this query as daysSince.

2. An query to generate your groups

SELECT PLSupplierAccountID, TransactionDate,
DSum("GoodsValueInAccountCurrency",
"daysSince",
"(no_of_days_since BETWEEN 1 AND 30) AND" &
"(PLSupplierAccountID = " & PLSupplierAccountID & ") AND " &
"(TransactionDate = " & TransactionDate & ")"
FROM PLPostedSupplierTran
GROUP BY PLSupplierAccountID, TransactionDate

Not tested, but it should show you the right direction.


mfG
-- stefan --

In article ,
says...
I am trying to age transactions into 30 days, 60 days etc and have put
together the following script but is giving a syntax error on "AS" on the 2nd
SUM line, can anyone please help me.

SELECT

PLSupplierAccountID, GoodsValueInAccountCurrency, TransactionDate,
DATEDIFF(DAY, 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
GROUP by PLSupplierAccountID, TransactionDate, GoodsValueInAccountCurrency


Maybe:

SELECT PLSupplierAccountID,
GoodsValueInAccountCurrency,
TransactionDate,
DATEDIFF(DAY,TransactionDate,GETDATE()) AS no_of_days_since,
SUM(IIF(DATEDIFF(DAY,TransactionDate,GETDATE()) BETWEEN 1
AND 30,
GoodsValueInAccountCurrency,0)) AS THIRTY,
SUM(IIF(DATEDIFF(DAY,TransactionDate,GETDATE()) BETWEEN 31
AND 60,
GoodsValueInAccountCurrency,0)) AS SIXTY,
SUM(IIF(DATEDIFF(DAY,TransactionDate,GETDATE()) BETWEEN 61
AND 90,
GoodsValueInAccountCurrency,0)) AS NINTY,
SUM(IIF(DATEDIFF(DAY,TransactionDate,GETDATE()) 90,
GoodsValueInAccountCurrency,0)) AS GreaterThenNinty
FROM PLPostedSupplierTran
GROUP BY PLSupplierAccountID,TransactionDate,GoodsValueInAc countCurrency

hi Wendy,

WendyUK wrote:
Thank you Stefan, this seems to work except for an error at the "FROM" is
that because there is syntax immediately before the from?
Yup, there is a closing ) missing for the DSum().


mfG
-- stefan --

You can use a crosstab with PARTITION in the PIVOT clause. Something like
(untested):



TRANSFORM SUM(GoodsValuesInAccountCurrency)
SELECT PLSupplierAccountID
FROM PLPostedSupplierTran
GROUP BY PLSupplierAccountID
PIVOT PARTITION(no_of_days_since, 0, 120, 30)




Hoping it may help,
Vanderghast, Access MVP

"WendyUK" wrote in message
news
I am trying to age transactions into 30 days, 60 days etc and have put
together the following script but is giving a syntax error on "AS" on the
2nd
SUM line, can anyone please help me.

SELECT

PLSupplierAccountID, GoodsValueInAccountCurrency, TransactionDate,
DATEDIFF(DAY, 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
GROUP by PLSupplierAccountID, TransactionDate, GoodsValueInAccountCurrency

Thank you Stefan, much appreciated

"Stefan Hoffmann" wrote:

hi Wendy,

WendyUK wrote:
Thank you Stefan, this seems to work except for an error at the "FROM" is
that because there is syntax immediately before the from?
Yup, there is a closing ) missing for the DSum().


mfG
-- stefan --