This discussion is archived
5 Replies Latest reply: Oct 9, 2013 3:27 AM by Purvesh K RSS

Analytical  Function

JoshMathew Newbie
Currently Being Moderated

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)

 

 

 

 

AmountRatePeriod in MonthsComputationNew Product
100010%31000*(10/100)*(3/12)=251025
102510%51025*(10/100)*(5/12)=42.71042.7
1042.78%21042.7*(8/100)*(2/12)=13.91056.6

 

 

Version  11.2.0.2

  • 1. Re: Analytical  Function
    adi999 Newbie
    Currently Being Moderated

    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
    Ramin Hashimzadeh Expert
    Currently Being Moderated

    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
    Ashu_Neo Pro
    Currently Being Moderated

    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
    Karthick_Arp Guru
    Currently Being Moderated

    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
    Purvesh K Guru
    Currently Being Moderated

    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points