Using Decode in oracle sql query for Debtors Analysis Report

5 pts.
Tags:
SQL
i have a table called transactions in my oracle database which stores data for employees under particular employers. each member has an account and employer_id with transactions , the table has fields as below.

TRAN_ID, TRANTYPE_ID, PENSION_ACCOUNT, EMPLOYER_ID, TRANAMT, SALARY, TAXEDAMOUNT, TRANDATE, POSTDATE, USER_ID, BATCH_ID, CONFIRMEDBY, CONFIRMDATE, COMMENTS, GLPOSTED, GLPOSTDATE, REVERSED, REVERSEDBY, REVERSEDATE, REVERSEREASON, REVERSEDTRAN_ID, RULE_ID, DISKBATCHHDR_ID

i want to develop a query with below layout

Employer_id     Total Amount   Last Contribution Date  Current  30dys  60dys 90 dys 120dys >120 dys

query to calculate aging = current_date - max(trandate)

the results ie 30 dys 60dys etc become columns showing amount owing in the categories

output  layout example

Employer_id Total_Amount Last Cont Date current 30dys 60dys 90dys

456             $345             30/05/2012     $100    $200   $45

457             $500             30/04/2012     $300    $200

Total           $ 845                                  $ 400    $ 200

 

 

 



Software/Hardware used:
Oracle 10 g, Toad ver 9 to connect to Database

Answer Wiki

Thanks. We'll let you know when a new response is added.

Something like this might help you:

<pre>decode(trunc((sysdate-trandate)/30),0,’ – 30 days’,1,’ – 60 days’,2,’ – 90 days’,’ – more than 90 days’)</pre>

This is just an idea for the DECODE part. You still need to figure out the way to use the max trandate, and any other additional requirement for the query.

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following