Results 1 to 3 of 3
  1. #1
    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. #2
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    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.
    Last edited by Peter.Vanroose; 09-30-08 at 03:32.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  3. #3
    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
  •