5 Replies Latest reply on Oct 9, 2013 10:27 AM by Purvesh K

# Analytical  Function

Hi,

Is  there  any  analytical  function  to  get  the result

3 different  rate 3  different  period

This  particular  loan  completes the  period  of  10  months.But  the  interest  is  computed  on  the  follwoing manner

For  the  first  slab (3  months)  rate is  10  %  and  in  the  subsequent  slabs  the  interest  should  be  added  along  with  the  principal( 1000)

 Amount Rate Period in Months Computation New Product 1000 10% 3 1000*(10/100)*(3/12)=25 1025 1025 10% 5 1025*(10/100)*(5/12)=42.7 1042.7 1042.7 8% 2 1042.7*(8/100)*(2/12)=13.9 1056.6

Version  11.2.0.2

• ###### 1. Re: Analytical  Function

Just write your own.......

create or replace FUNCTION f_Interest(amount INTEGER, rate INTEGER, period INTEGER, totalDuration INTEGER) RETURN INTEGER

AS

interest INTEGER;

BEGIN

interest := amount * (rate/100) * (period / totalDuration);

RETURN interest + amount;

END;

select f_Interest(1000,10,3,12) from dual;

F_INTEREST(1000,10,3,12)

------------------------

1025

Obviously if you want better precision you might want to use something other than integers, but you get the idea.

• ###### 2. Re: Analytical  Function

Your question is not clear. Please explain more exactly. And please note that If you are planning develop loans module, the good way calculate interest everyday. And stores balance for every day.

---

Ramin Hashimzade

• ###### 3. Re: Analytical  Function

Not sure with your requirement! But this is some what, you asked for?

WITH loan (amount,rate,prd_in_mon)
AS
(
SELECT 1000,10, 3 FROM dual UNION ALL
SELECT 1025,10, 5 FROM dual UNION ALL
SELECT 1042.7,8,2 FROM dual
)
SELECT amount,
rate,
prd_in_mon,
round(amount*(rate/100)*(prd_in_mon/12),1) calc,
first_value(amount IGNORE NULLS) OVER() + round((amount*(rate/100)*(prd_in_mon/12)),1) prod_new_val
FROM loan
;

OP:-
AMOUNT RATE   PRD_IN_MON  CALC   PROD_NEW_VAL
--------------------------------------------
1000   10     3           25     1025
1025   10     5           42.7   1042.7
1042.7 8      2           13.9   1013.9

Add on: - In your expected output, last line amount shows wrong data( in your terms), as it should be added to principle value. i.e. 1000.

Thanks!

• ###### 4. Re: Analytical  Function

SQL> with t
2  as
3  (
4  select 1000 amt, 10 rt, 3 period_in_month from dual
5  union all
6  select 1025 amt, 10 rt, 5 period_in_month from dual
7  union all
8  select 1042.7 amt, 8 rt, 2 period_in_month from dual
9  )
10  select amt
11       , rt
12       , period_in_month
13       , intr_calc
14       , case when period_t <= 3 then
15                  (amt - nvl(lag(intr_calc) over(order by amt), 0)) + intr_calc
16              else
17                  (amt - nvl(lag(intr_calc) over(order by amt), 0)) +
18                   sum(case when period_t <= 3 then 0 else intr_calc end) over(order by amt)
19         end new_prod
20    from (
21            select amt
22                 , rt
23                 , period_in_month
24                 , round(amt*(rt/100)*(period_in_month/12), 1) intr_calc
25                 , sum(period_in_month) over(order by amt) period_t
26              from t
27         );

AMT         RT PERIOD_IN_MONTH  INTR_CALC   NEW_PROD
---------- ---------- --------------- ---------- ----------
1000         10               3         25       1025
1025         10               5       42.7     1042.7
1042.7          8               2       13.9     1056.6

• ###### 5. Re: Analytical  Function

Another solution using Model clause:

select amount, rate, prd, round(new_amt, 4) new_amount

from test_table

model

dimension by (rownum r)

measures (amount, rate, prd, new_amt)

rules update

(

new_amt[ANY]=amount[cv()] + amount[cv()]*(rate[cv()]/100) * (prd[cv()]/12)

)

AMOUNT                 RATE                   PRD                    NEW_AMOUNT

---------------------- ---------------------- ---------------------- ----------------------

1000                   10                     3                      1025

1025                   10                     5                      1067.7083

1042.7                 8                      2                      1056.6027