Using Decode in oracle sql query for Debtors Analysis Report
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
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
ASKED:
June 14, 2012 8:35 AM
UPDATED:
June 15, 2012 3:41 PM
Answer Wiki:
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.
To see all answers submitted to the Answer Wiki: View Answer History.
RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
