
1. Re: Getting multiplication of column values on a joined table based on condition
Etbin Sep 10, 2013 9:04 AM (in response to Mikail)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_YEAR RATE INDEXED_RATE 2008 1.057 1.05699999999999999999999999999999999999 2007 1.208 1.27685600000000000000000000000000000001 2006 1.067 1.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_YEAR RATE INDEXED_RATE 2006 1.067 1.362405352 2007 1.208 1.276856 2008 1.057 1.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_YEAR RATE INDEXED_RATE 2008 1.057 1.057 2007 1.208 1.276856 2006 1.067 1.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 Sep 10, 2013 7:56 AM (in response to Mikail)1 person found this helpfulCan 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 Sep 10, 2013 8:00 AM (in response to Etbin)1 person found this helpfulAlmost.
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 Sep 10, 2013 8:00 AM (in response to Mikail)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 Sep 10, 2013 8:12 AM (in response to Chris Hunt)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 Sep 10, 2013 8:56 AM (in response to 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