# Thread: aging report 30, 60, 90 - how to

1. Registered User
Join Date
Sep 2008
Posts
34

## Unanswered: aging report 30, 60, 90 - how to

I have a table with fields - invoicenumber,customercode,invoicedate,balanceforw ard.

I want to know the sql to generate a report showing the totals for the top ten customers by total dollars outstanding, showing each invoice age range, grouped by customercode.

So if I have a table with data like so...

invoicenumber, customercode, invoicedate, balanceforward
00001 ABC 07/01/08 100.00
00002 ABC 08/01/08 150.00
00003 DEF 08/02/08 150.00
00004 ABC 09/15/08 100.00
00005 GHI 09/16/08 200.00
00006 DEF 09/16/08 100.00
00007 ABC 09/17/08 100.00

I'd like to see the data like so...

customercode, 0-30 31-60 61>
ABC 200.00 150.00 100.00
DEF 100.00 150.00 0.00
GHI 200.00 0.00 0.00

I know how to show the daysold by calculating it, but I don't know how to summarize it.

Any help with the SQL statement would be greatly apreciated.

I am running PHP on IIS accessing data on an AS400 running DB2.

I

2. Registered User
Join Date
Sep 2004
Location
Belgium
Posts
1,126
Originally Posted by curtmorehouse
I'd like to see the data like so...

customercode, 0-30 31-60 61>
ABC 200.00 150.00 100.00
DEF 100.00 150.00 0.00
GHI 200.00 0.00 0.00
Code:
```SELECT customercode,
SUM(CASE WHEN invoicedate >= current date - 30 days
THEN balanceforward ELSE 0 END) AS "0-30",
SUM(CASE WHEN invoicedate BETWEEN current date - 60 days
AND current date - 31 days
THEN balanceforward ELSE 0 END) AS "31-60",
SUM(CASE WHEN invoicedate < current date - 60 days
THEN balanceforward ELSE 0 END) AS "61>",
SUM(balanceforward) AS total_outstanding
FROM   MyTable
GROUP BY customercode
ORDER BY total_outstanding DESC
FETCH FIRST 10 ROWS ONLY```
Does your version of DB2 have the OLAP function rank()?
In that case you could consider using it instead of the "ORDER BY / FETCH FIRST" combination: it will allow showing "ties". Now, if e.g. 10th and 11th customer in the ordered list have the same total_outstanding, "randomly" one of the two will be shown.
Last edited by Peter.Vanroose; 09-30-08 at 02:32.

3. Registered User
Join Date
Sep 2008
Posts
34

## It works! Thanks a ton!

I had to make one small change on your code, which was to escape the quotes around 0-30, etc because I am using the SQL statement in PHP, but other than that, it worked perfectly!!!!

Thanks you very much!!!!!

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•