aging report 30, 60, 90 - how to

curtmorehouse · #1 (**permalink**) 09-29-08, 22:37

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

Peter.Vanroose · #2 (**permalink**) 09-30-08, 02:28

Quote:

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.

curtmorehouse · #3 (**permalink**) 09-30-08, 10:42

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

Internet Services

Web Development

Digital Marketing

Consumer Tech