Home » SQL & PL/SQL » SQL & PL/SQL » projected value calculation
projected value calculation [message #274162] Sun, 14 October 2007 20:47 Go to next message
vipin
Messages: 40
Registered: May 2002
Member
Hi all,

I have been trying to create a report on a computed column using analytical function but it looks like I am missing something. The goal is to list the depreciated value for each month. Below is the test sql.

DROP TABLE ASSET_DETAILS;
CREATE TABLE ASSET_DETAILS (
DEPT_ID varchar2(5),
ASSET_ID varchar2(5),
ASSET_VALUE NUMBER(10, 2),
DOP date, --date of purchase
EOL number(2), -- enf of life in months
DEPR_PERCENT NUMBER(5,2)
);

INSERT INTO ASSET_DETAILS VALUES ('D03', 'A03', 23000, TO_DATE('OCT200710', 'MONYYYYDD'), 6, 7);

select
dept_id,
asset_id,
depr_date,
asset_value,
lead (asset_value * (100-DEPR_PERCENT)/100) over (PARTITION BY dept_id, asset_id ORDER BY depr_date, dept_id, asset_id ) as DEPR_VALUE,
DOP, depr_percent
from asset_details,
(select last_day(add_months(sysdate, rownum)) depr_date from user_objects where rownum <=6) temp_tbl
where LAST_DAY(add_months (dop, eol+1)) >= TRUNC(sysdate) and LAST_DAY(add_months(dop, eol+1)) >= depr_date
;

 
DEPT_ID    ASSET_ID   DEPR_DATE          ASSET_VALUE           DEPR_VALUE DOP               DEPR_PERCENT
---------- ---------- --------- -------------------- -------------------- --------- --------------------
D03        A03        30-NOV-07                23000                21390 10-OCT-07                    7
D03        A03        31-DEC-07                23000                21390 10-OCT-07                    7
D03        A03        31-JAN-08                23000                21390 10-OCT-07                    7
D03        A03        29-FEB-08                23000                21390 10-OCT-07                    7
D03        A03        31-MAR-08                23000                21390 10-OCT-07                    7
D03        A03        30-APR-08                23000                      10-OCT-07                    7


Going by the example the expected result for depr_value is

Nov - 21390
Dec - 20748.3
Jan - 20125.85
--
--

Could you please help?

Thanks,
Re: projected value calculation [message #274181 is a reply to message #274162] Sun, 14 October 2007 22:52 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
lead (asset_value * (100-DEPR_PERCENT)/100) just looks at the ASSET_VALUE and DEPR_PERCENT of the following row. Since both of those values are effectively constants (all rows have the same value), it will always return the same result for every row.

What you want (I think) is actually a RECURSIVE calculation, where the result of a calculation on one row is used to seed the same calculation for the next row.

I have my suspicions that recursion is impossible with analytic functions, but I lack the mathematical rigour to prove it.

There are three techniques you can use to preform a recursive operation:
- CONNECT BY
- MODEL
- PL/SQL

Any of these could be used to solve your problem, but that does not mean that all of them are appropriate. You have to consider the appropriateness of the solution.

CONNECT BY will produce a solution that noone will ever be able to understand. CONNECT BY is intended for hierarchical tree retrieval; using it to emulate recursion just because it CAN is a poor use of the technology. It is neither intuitive nor scalable.

MODEL will produce a short elegant solution, but is not always intuitive. I don't use it, but don't let that stop you. You may need your DBA to set up an analytic workspace to use MODEL; like I said, I don't use it.

With the PL/SQL option, you would create a PIPELINED TABLE FUNCTION (do a search) to retrieve the raw data, and then use PL/SQL to perform the calculation row-by-row and PIPE ROW to feed the results to the calling SQL statement. This provides the most straightforward and intuitive solution. It involves a bit more code than MODEL, but can be made more efficient if you can get an INDEX to perform the sort for you.

Ross Leishman
Re: projected value calculation [message #274195 is a reply to message #274162] Mon, 15 October 2007 01:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can do something like the following (as I don't understand if the depreciation is from the last value or the original one, I gave 2 values but if this is not correct you can just change the formula):
SQL> with 
  2    data as (select 'A03' id, 23000 val, TO_DATE('OCT200710', 'MONYYYYDD') dop, 7 dpct from dual),
  3    lines as (select level-1 line from dual connect by level <= 6)
  4  select id, add_months(dop,-1*line) ddate, val,
  5         to_char(val*power(1-dpct/100,line),'99990D00') dval1,
  6         to_char(val-line*dpct*val/100,'99990D00') dval2
  7  from data, lines
  8  /
ID  DDATE              VAL DVAL1     DVAL2
--- ----------- ---------- --------- ---------
A03 10-OCT-2007      23000  23000.00  23000.00
A03 10-SEP-2007      23000  21390.00  21390.00
A03 10-AUG-2007      23000  19892.70  19780.00
A03 10-JUL-2007      23000  18500.21  18170.00
A03 10-JUN-2007      23000  17205.20  16560.00
A03 10-MAY-2007      23000  16000.83  14950.00

6 rows selected.

Regards
Michel
Re: projected value calculation [message #274204 is a reply to message #274195] Mon, 15 October 2007 01:40 Go to previous messageGo to next message
vipin
Messages: 40
Registered: May 2002
Member
Hi Michael, Ross,

Thank you for the reply. I read your posts and when you say it is conclusive.Razz

I think analytics is not a good choice for computed value. Shoud Oracle consider this? I have used row_number in combination with user defined function to get the result. I believe the performance is not bad. Your comments please.

CREATE OR REPLACE FUNCTION FUNC_CALC_DEPR (
depr_date date,
dop date,
eol number,
depr_percent number,
asset_value number,
depr_type varchar2 -- 'S' for straightline depreciation; 'R' for reducing balance
) return number
as
depr_value number(30,2) := 0;
eol_calc number(5);
eol_date date;
begin

if depr_type = 'S' then
begin

eol_calc := ((eol * eol) - 1 + eol +1)/2; -- formula to calculate the series sum( L - F + F + L)/2
eol_date := last_day(add_months(dop, EOL+1)); --+1 added as monthend is taken for calculation

select (asset_value * (months_between (eol_date, depr_date)/eol_calc)) into depr_value from dual;

end;

elsif depr_type = 'R' then

begin

depr_value := asset_value;

for i in 1..eol loop

depr_value := (depr_value * (100-DEPR_PERCENT)/100);

end loop;

end;

end if;

return depr_value;
end;
/
show error

-- reducing balance calculation - uses row_number analytic function to order the record by date
select
to_char(depr_date, 'MON-YYYY') depr_date, dept_id, asset_id, asset_value, depr_value,
DOP, EOL
from
(
select depr_date, dept_id, asset_id, asset_value,
func_calc_depr(depr_date, dop, row_number() over (PARTITION BY dept_id, asset_id ORDER BY depr_date), depr_percent, asset_value, 'R') depr_value,
DOP, EOL from
asset_details,(select last_day(add_months(sysdate, rownum)) depr_date from user_objects where rownum <=6) temp_tbl
where
LAST_DAY(add_months (dop, eol+1)) >= TRUNC(sysdate) and LAST_DAY(add_months(dop, eol+1)) >= depr_date
order by depr_date
)
;




DEPR_DATE       DEPT_ID    ASSET_ID            ASSET_VALUE   DEPR_VALUE DOP             EOL
--------------- ---------- ---------- -------------------- ------------ --------------- ---
NOV-2007        D03        A03                       23000     21390.00 10-OCT-07         6
DEC-2007        D03        A03                       23000     19892.70 10-OCT-07         6
JAN-2008        D03        A03                       23000     18500.21 10-OCT-07         6
FEB-2008        D03        A03                       23000     17205.20 10-OCT-07         6
MAR-2008        D03        A03                       23000     16000.84 10-OCT-07         6
APR-2008        D03        A03                       23000     14880.78 10-OCT-07         6
Re: projected value calculation [message #274210 is a reply to message #274204] Mon, 15 October 2007 01:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Can you post your code/query between code tags with indentation and lines of no more than 80 characters?

Regards
Michel
Re: projected value calculation [message #274217 is a reply to message #274210] Mon, 15 October 2007 02:07 Go to previous messageGo to next message
vipin
Messages: 40
Registered: May 2002
Member
Sorry, I missed it.


CREATE OR REPLACE  FUNCTION FUNC_CALC_DEPR (
   depr_date 		date,
   dop  		date,
   eol  	  	number,
   depr_percent 	number, 
   asset_value 		number,
   depr_type  		varchar2 -- 'S' for straightline depreciation; 'R' for reducing balance 
) return number
as
   depr_value number(30,2) := 0;
   eol_calc  number(5);
   eol_date  date;
begin

if depr_type = 'S' then 
begin

eol_calc := ((eol * eol) - 1 + eol +1)/2; -- formula to calculate the series sum( L - F + F + L)/2
eol_date := last_day(add_months(dop, EOL+1)); --+1 added as monthend is taken for calculation

select (asset_value * (months_between (eol_date, depr_date)/eol_calc)) into depr_value from dual;

end;

elsif depr_type = 'R' then 

begin

depr_value := asset_value;

for i in 1..eol loop

depr_value := (depr_value * (100-DEPR_PERCENT)/100);

end loop;

end;

end if;

return depr_value;
end;
/
show error

Re: projected value calculation [message #274225 is a reply to message #274217] Mon, 15 October 2007 02:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And you call this indented? And remove useless empty lines.

Regards
Michel
Re: projected value calculation [message #274231 is a reply to message #274225] Mon, 15 October 2007 02:56 Go to previous messageGo to next message
vipin
Messages: 40
Registered: May 2002
Member
Hi Michael,

I have left those gaps for clarity. Here is the modified one. Hope it is better now.
Thanks,

CREATE OR REPLACE  FUNCTION FUNC_CALC_DEPR (
   depr_date 		date,
   dop  		date,
   eol  	  	number,
   depr_percent 	number, 
   asset_value 		number,
   depr_type  		varchar2 -- 'S' for straightline depreciation; 'R' for reducing balance 
) return number
as
   depr_value number(30,2) := 0;
   eol_calc  number(5);
   eol_date  date;
begin

if depr_type = 'S' then 
begin
  eol_calc := ((eol * eol) - 1 + eol +1)/2; -- formula to calculate the series sum( L - F + F + L)/2
  eol_date := last_day(add_months(dop, EOL+1)); --+1 added as monthend is taken for calculation
  select (asset_value * (months_between (eol_date, depr_date)/eol_calc)) into depr_value from dual;
end;
elsif depr_type = 'R' then 
begin
   depr_value := asset_value;
   for i in 1..eol loop
      depr_value := (depr_value * (100-DEPR_PERCENT)/100);
   end loop;
end;
end if;

return depr_value;
end;
/
show error

--reducing balance with accumulated depreciation using sum analytic function 
select 
   to_char(depr_date, 'MON-YYYY') depr_date, dept_id, asset_id, asset_value, depr_value, Accum_depr
from (
   select 
      temp_tbl2.*, sum(asset_value - depr_value) over (PARTITION BY dept_id, asset_id ORDER BY depr_date ) as Accum_depr
   from 
   (select depr_date, dept_id, asset_id, asset_value,
    func_calc_depr(depr_date, dop, row_number() over (PARTITION BY dept_id, asset_id ORDER BY depr_date), depr_percent, asset_value, 'R') depr_value, 
    DOP, EOL from 
    asset_details,(select last_day(add_months(sysdate, rownum)) depr_date from user_objects where rownum <=6) temp_tbl1
where 
   LAST_DAY(add_months (dop, eol+1)) >= TRUNC(sysdate) and LAST_DAY(add_months(dop, eol+1)) >= depr_date and dop <= depr_date) temp_tbl2 
   order by depr_date
);
Re: projected value calculation [message #274235 is a reply to message #274231] Mon, 15 October 2007 03:15 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Get rid of the SELECT from the function. If you can do it in pure PL/SQL it will be a lot more scalable.

Ross Leishman
Re: projected value calculation [message #274248 is a reply to message #274225] Mon, 15 October 2007 04:01 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Go easy on him Michel
He's provided a clear description of his problem and a set of create/insert scripts, which puts him in the top 1% of posters.

Re: projected value calculation [message #274250 is a reply to message #274248] Mon, 15 October 2007 04:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
He is actually in the top 1%, I want him in the top 0.1%. Smile

Regards
Michel
Re: projected value calculation [message #274255 is a reply to message #274250] Mon, 15 October 2007 04:24 Go to previous messageGo to next message
vipin
Messages: 40
Registered: May 2002
Member
Nice goal Michel. In fact, I like that discipline.

Cheers.
Re: projected value calculation [message #274300 is a reply to message #274248] Mon, 15 October 2007 07:51 Go to previous message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
JRowbottom wrote on Mon, 15 October 2007 05:01

Go easy on him Michel
He's provided a clear description of his problem and a set of create/insert scripts, which puts him in the top 1% of posters.



And he used TO_DATE properly.
Previous Topic: is no_data_found needed for this proc (merged)
Next Topic: How to do Bulk copy in PL/SQL
Goto Forum:
  


Current Time: Mon Dec 05 21:28:37 CST 2016

Total time taken to generate the page: 0.16606 seconds