bvRocks
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
;
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
ASKER
Thanks so much for giving the precious time. Much appreciated!