Ageing by Date to 30, 60,90 etc

[WendyUK]

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

 

[Ofer Cohen]

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

 

[Stefan Hoffmann]

hi Wendy,

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 <--

 

[WendyUK]

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

 

[WendyUK]

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

 

[WendyUK]

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

 

[Michael Gramelspacher]

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,GoodsValueInAccountCurrency

 

[Stefan Hoffmann]

hi Wendy,

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 <--

 

[Michel Walsh]

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]

Thank you Stefan, much appreciated

hi Wendy,

Yup, there is a closing ) missing for the DSum().


mfG
--> stefan <--