[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1305
  • Last Modified:

Aging Report in ORACLE or MSSQL

Hi all,

I have a bit of a scenario for which I'd really appreciate some help.

I have a database that has client invoices that are churned out on a monthly basis, clients pay against these invoices. However, some clients sometimes don't pay or pay less than the invoiced amounts, some still, pre-pay.

Now, I need to do a debtors aging report showing the distrubution of debts between today (current) and over120 days. I'm able to pull out all my invoiced amounts correctly to allocate them to the correct time bands (30days, 60days, 90days, 120days and over120) but I keep getting errors when it comes to over120 days payments.

Any chance someone has the English query for aging so that I could adapt it to either Oracle or SQL?

Any help offered most appreciated!

Regards,
Gezzat
0
Gezzat
Asked:
Gezzat
1 Solution
 
aabbasCommented:
Oracle's DECODE function will do this. Following is a simple query to age amount in <30, >=30, >=60, >=90, >=120 days.

select invoice_no, invoice_date, floor (sysdate - invoice_date) "DAYS",
       decode (floor (floor (sysdate - invoice_date) / 30), 0, amt, null) "<30",
       decode (floor (floor (sysdate - invoice_date) / 30), 1, amt, null) ">=30",
       decode (floor (floor (sysdate - invoice_date) / 30), 2, amt, null) ">=60",
       decode (floor (floor (sysdate - invoice_date) / 30), 3, amt, null) ">=90",
       decode (floor (floor (sysdate - invoice_date) / 30), 4, amt, null) ">=120"
from inv;

Above query uses a simple table with Invoice_No, Invoice_Date and Amt columns.

Try it out. Hope, it will work for you.

Best of luck.
0
 
GezzatAuthor Commented:
Thanks Abbas! lemmie try this and get back to you!
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now