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

    Analytical  Function

    Josh Mathew

      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

          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

            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

              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_

                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

                  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