6 Replies Latest reply: Sep 10, 2013 3:56 AM by Mikail RSS

    Getting multiplication of column values on a joined table based on condition

    Mikail

      The title was best I could find to explain my question, I don't think it helps though. Anyways, I have two tables:

      CREATE TABLE YEARLY_RATES

      ( RATE_YEAR NUMBER(4,0),

      RATE NUMBER(6,4)

      );

        CREATE TABLE PERSON_ACCOUNT

      ( PAID_YEAR NUMBER(4,0),

      PAID_AMOUNT(10,2)

      );

      I need to select PERSON_ACCOUNT.PAID_YEAR, PERSON_ACCOUNT.PAID_AMOUNT,PERSON_ACCOUNT.PAID_AMOUNT * multiplication of the YEARLY_RATES.RATE WHERE YEARLY_RATES.RATE_YEAR >= PERSON_ACCOUNT.PAID_YEAR. So for instance if I have the following data in PERSON_ACCOUNT

      PAID_YEAR|PAID_AMOUNT

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

      2006   2150.69

      2007   3290.40

      2008   3000.35

      and YEARLY_RATES tables

      RATE_YEAR|RATE

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

      2006   1.067

      2007   1.208

      2008   1.057

      then I should be able to have the following output:

      PAID_YEAR|PAID_AMOUNT|INDEXED_AMOUNT

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

      2006    2150.69    2150.69*1.067*1.208*1.057

      2007    3290.40    3290.40*1.208*1.057

      2008    3000.35    3000.35*1.057

        • 1. Re: Getting multiplication of column values on a joined table based on condition
          Etbin

          with

          yearly_rates as

          (select 2006 RATE_YEAR,1.067 RATE from dual union all

          select 2007,1.208 from dual union all

          select 2008,1.057 from dual

          )

          select rate_year,rate,exp(sum(ln(rate)) over (order by rate_year desc)) indexed_rate

            from yearly_rates

           

          RATE_YEARRATEINDEXED_RATE
          20081.0571.05699999999999999999999999999999999999
          20071.2081.27685600000000000000000000000000000001
          20061.0671.36240535200000000000000000000000000001

           

          Regards

           

          Etbin


          model solution looks cleaner


          select rate_year,rate,indexed_rate

            from yearly_rates

          model dimension by (rate_year)

                 measures (rate,1 indexed_rate)

          rules

                 (

                  indexed_rate[any] order by rate_year desc = rate[cv()] * nvl(indexed_rate[cv() + 1],1)

                 )

           

          RATE_YEARRATEINDEXED_RATE
          20061.0671.362405352
          20071.2081.276856
          20081.0571.057

           

          Updated the first solution as suggested by ChrisHunt


          How about recursive with


          with

          yearly_rates as

          (select 2006 rate_year,1.067 rate from dual union all

          select 2007,1.208 from dual union all

          select 2008,1.057 from dual

          ),

          indexed_rates(rate_year,rate,indexed_rate) as

          (select rate_year,rate,indexed_rate

             from (select rate_year,rate,rate indexed_rate,row_number() over (order by rate_year desc) step

                     from yearly_rates

                  )

            where step = 1

          union all

          select yr.rate_year,yr.rate,ir.indexed_rate * yr.rate

             from indexed_rates ir,

                  yearly_rates yr

            where yr.rate_year = ir.rate_year - 1

          )

          select rate_year,rate,indexed_rate

            from indexed_rates

           

          RATE_YEARRATEINDEXED_RATE
          20081.0571.057
          20071.2081.276856
          20061.0671.362405352

           

          Message was edited by: Etbin Recursive with solution added

          • 2. Re: Getting multiplication of column values on a joined table based on condition
            sagargole

            Can you write a database function which will accept paid_year and paid_amount as in parameters. The function can then loop and select RATE from the YEARLY_RATES table where RATE_YEAR >= PAID_YEAR (Input parameter). The product can be stored in a variable until all the eligible records in the YEARLY_RATES are traversed. The variable can be returned from the function.

             

            You can then use the following SQL.

             

            SELECT paid_year, paid_amount, fnc_get_idx_amt(paid_year, paid_amount) FROM person_account.

            • 3. Re: Getting multiplication of column values on a joined table based on condition
              Chris Hunt

              Almost.

               

              with

              yearly_rates as

              (select 2006 RATE_YEAR,1.067 RATE from dual union all

              select 2007,1.208 from dual union all

              select 2008,1.057 from dual

              )

              select rate_year,rate,exp(sum(ln(rate)) over (order by rate_year desc)) indexed_rate

                from yearly_rates

                order by rate_year

              • 4. Re: Getting multiplication of column values on a joined table based on condition
                Paul  Horth

                Try this:

                 

                with person_account as (
                select 2006 paid_year, 2150.69 paid_amount from dual
                union
                select 2007 paid_year, 3290.40 paid_amount from dual
                union
                select 2008 paid_year, 3000.35 paid_amount from dual
                ),
                yearly_rates as (
                select 2006 rate_year, 1.067 rate from dual
                union
                select 2007 rate_year, 1.208 rate from dual
                union
                select 2008 rate_year, 1.057 rate from dual
                )
                select yr.rate_year, power(10,log(10,pa.paid_amount)+sum(log(10,yr.rate)) over (order by yr.rate_year
                range between current row and unbounded following))
                from person_account pa
                inner join yearly_rates yr
                on yr.rate_year = pa.paid_year
                
                • 5. Re: Getting multiplication of column values on a joined table based on condition
                  Etbin

                  Thank you for pointing out my error I updated my post

                  Might have something to do with being on vacation

                   

                  Regards

                   

                  Etbin

                  • 6. Re: Getting multiplication of column values on a joined table based on condition
                    Mikail

                    Thank you all for replying. After taking a look at ChrisHunt's answer I got ashamed on how low level of Oracle knowledge I have, because I was not aware of any of the techniques. But I'll post my own way of solving the problem too, to maybe provide an alternative:

                     

                        SELECT PA.PAID_YEAR,PA.PAID_AMOUNT,EXP(SUM(LN(YR.RATE)))*PA.PAID_AMOUNT
                       
                    FROM YEARLY_RATES YR, PERSON_ACCOUNT PA
                       
                    WHERE YR.RATE_YEAR>=PA.PAID_YEAR
                       
                    GROUP BY PA.PAID_YEAR,PA.PAID_AMOUNT