Ageing by Date to 30, 60,90 etc

W

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
 
O

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
 
S

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

WendyUK

Ofer Cohen said:
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
 
W

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

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
 
S

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

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top