This discussion is archived
6 Replies Latest reply: Sep 10, 2013 1:56 AM by Mikail RSS

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

Mikail Newbie
Currently Being Moderated

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

    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 Newbie
    Currently Being Moderated

    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 Journeyer
    Currently Being Moderated

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

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

    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 Newbie
    Currently Being Moderated

    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

Legend

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