Code Needed for Accounts Receivable Aging Report 2

[acct98]

Im not try to recreate the wheel. Does anyone have SQL code to create an aging report?

 

[SantaMufasa]

Acct98,

Your specifications are pretty thin here, but I'll propose a solution that you can either accept, reject, or refine. Since you are posting in an Oracle 9i Forum, I'll offer a solution that uses the CASE statement:

Section 1 -- Sample data:

col purchase_dt heading "Purchase|Date" format a9
col amount format 999,999.99
select * from purchase;

                                  Purchase
        ID    CUST_ID      AMOUNT Date
---------- ---------- ----------- ---------
         1         10      345.67 23-OCT-04
         2         10      275.15 06-OCT-04
         3         10      515.40 22-SEP-04
         4         10      127.84 12-SEP-04
         5         10       38.50 19-AUG-04
         6         10      122.48 26-FEB-04
         7         15      140.20 28-OCT-04

7 rows selected.

Section 2 -- Sample aging query and results:

col a heading "0 - 29|Days" format 999,999.99
col b heading "30 - 59|Days" format 999,999.99
col c heading "60 - 89|Days" format 999,999.99
col d heading "Over 90|Days" format 999,999.99
select cust_id
      ,sum(case when sysdate-purchase_dt between 0 and 29
           then amount
           else 0 end) a
      ,sum(case when sysdate-purchase_dt between 30 and 59
           then amount
           else 0 end) b
      ,sum(case when sysdate-purchase_dt between 60 and 89
           then amount
           else 0 end) c
      ,sum(case when sysdate-purchase_dt > 90
           then amount
           else 0 end) a
from purchase
group by cust_id
/

                0 - 29     30 - 59     60 - 89      0 - 29
   CUST_ID        Days        Days        Days        Days
---------- ----------- ----------- ----------- -----------
        10      620.82      643.24       38.50      122.48
        15      140.20         .00         .00         .00

2 rows selected.

Let us know if this is what you wanted.

Mufasa
(aka Dave of Sandy, Utah, USA)
@ 19:41 (02Nov04) UTC (aka "GMT" and "Zulu"),
@ 12:41 (02Nov04) Mountain Time

 

[acct98]

This is what I was looking for.

Thanks

 

[BackStageJim]

Sweet. Just what I needed to fix an statement of account report for next week.