| Aging report logic [message #288156] |
Sat, 15 December 2007 03:00  |
rafi_aamiri
Messages: 34
Registered: September 2005
|
Member |
|
|
Hi,
There is an requirement where i need to generate report on aging..
ie.. amount unpaid by the customer to be identified on different aging
0-15 days 16-30 days 31-45 days 56-90 days
so the output of the report show be like above on different aging period.
this logic need to be implemented using decode function in a single select statement
if not do let me know the other ways to implement in oracle reports.
pls help me in this regard
|
|
|
|
|
|
|
|
| Re: Aging report logic [message #288206 is a reply to message #288196] |
Sat, 15 December 2007 20:15  |
dude4084
Messages: 222
Registered: March 2005
Location: Mux
|
Senior Member |
|
|
Hi
I have tried to find the solution of your requirement.
Interestingly i have not used DECODE in the solution as REPORTS has its own capability in matrix report.
But if you are interested to use DECODE then you have to go SQL forum.
Here is solution for you
===========================
As you have not provided any table and rows, so i have decided to use emp table in a way that
sal = amount due
hiredate = readjusted so that the hiredate should be for year 2007
select ename , sal,
to_date(to_char(hiredate, 'DD-MON') ||'-07', 'DD-MON-YY') due, trunc(sysdate)-to_date(to_char(hiredate, 'DD-MON') ||'-07',
'DD-MON-YY') age
from emp
/
ENAME SAL DUE AGE
---------- ---------- --------- ----------
SMITH 800 17-DEC-07 -1
ALLEN 1600 20-FEB-07 299
WARD 1250 22-FEB-07 297
JONES 2975 02-APR-07 258
MARTIN 1250 28-SEP-07 79
BLAKE 2850 01-MAY-07 229
CLARK 2450 09-JUN-07 190
SCOTT 3000 19-APR-07 241
KING 5000 17-NOV-07 29
TURNER 1500 08-SEP-07 99
ADAMS 1100 23-MAY-07 207
JAMES 950 03-DEC-07 13
FORD 3000 03-DEC-07 13
MILLER 1300 23-JAN-07 327
The negative age shown for Smith so i am using ABS function.
You have made group over range of 15. I will use over range of 50.
Now see the attached file
-
Attachment: test4.RDF
(Size: 56.00KB, Downloaded 1230 times)
|
|
|
|
| Re: Aging report logic [message #288207 is a reply to message #288156] |
Sat, 15 December 2007 20:25 |
dude4084
Messages: 222
Registered: March 2005
Location: Mux
|
Senior Member |
|
|
I have worked a little bit more in order to have logical headings. So here is modified version.
By the way if you want to use decode n SQL then you can do it with following pseducode
1) Calculate age
2) ceil(age / 15) -- as 15 was your original range
3) use deocde on ceil(age / 15) i.e.
decode( ceil(age / 15),
1, '0-15 days',
2, '16-30 days',
3, '31-45 days',
4, '46-60 days',
5, '61-75 days',
'75+ days')
Good luck!
-
Attachment: test5.RDF
(Size: 52.00KB, Downloaded 1398 times)
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
