Link to home
Start Free TrialLog in
Avatar of bvRocks
bvRocksFlag for Afghanistan

asked on

Calculate Inflation

I have the below requirement to fulfil. How to achieve this simplistically.

There is an inflation rate table as below

2009, 5%
2010, 5%
2011, 5%
2012, 5%
2013, 5%

I need to first get
2009, 5%, 5
2010, 5%, 5.25             - which is 5% over 5
2011, 5%, 5.5125         - which is 5% over 5.25
2012, 5%, 5.788125     - which is 5% over 5.5125
2013, 5%, 6.07753125 - which is 5% over 5.788125

And then get
2009, 5%, 5                  , 5
2010, 5%, 5.25             , 10.25              = 5.25 + 5
2011, 5%, 5.5125         , 15.7625          = 5.5125 + 10.25 and so on
2012, 5%, 5.788125     , 21.550625
2013, 5%, 6.07753125 , 27.62815625

Using SQL Server 2008 R2 or above
ASKER CERTIFIED SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Scott, I did not see your post.  I think I like the use of the compound interest formula better than my approach.  I think I saw a table and thought the rates could change each year.
Assuming the rate of 5% is stored as .05 -
select x.year, x.rate, power(1 + x.rate,rn - 1) * 100 * x.rate as newrate, 100 * (power(1 + x.rate, x.rn) - 1) as cum_rate from
(select year, rate, row_number() over (order by year) rn
 from inflation_rate) as x
;
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of bvRocks

ASKER

Trying to understand CROSS_APPLY... What does it do?
Ref: http://technet.microsoft.com/en-us/library/ms175156(v=sql.105).aspx

In the above, it allows you formulas across each row of the query like you would in the SELECT; however, performing these functions in a CROSS APPLY allows you to use the derived column name in subsequent portions of the query.  For example, the year_count appears in the current and previous interest formulas.
Avatar of bvRocks

ASKER

Thanks so much for giving the precious time. Much appreciated!