Solved

Calculate Inflation

Posted on 2013-12-18
213 Views
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
0
Question by:bvRocks
• 3
• 2
• 2
• +1

LVL 69

Accepted Solution

ScottPletcher earned 167 total points
DECLARE @start_year int
SET @start_year = 2009

SELECT
yi.year, yi.int_rate * 100 AS int_rate, prev_yr.prev_yr_int, curr_yr.curr_yr_int
FROM (
SELECT 2009 AS year, CAST(0.05 AS float) AS int_rate UNION ALL
SELECT 2010 AS year, CAST(0.05 AS float) AS int_rate UNION ALL
SELECT 2011 AS year, CAST(0.05 AS float) AS int_rate UNION ALL
SELECT 2012 AS year, CAST(0.05 AS float) AS int_rate UNION ALL
SELECT 2013 AS year, CAST(0.05 AS float) AS int_rate
) AS yi
CROSS APPLY (
SELECT yi.year - @start_year + 1 AS year_count
) AS year_counts
CROSS APPLY (
SELECT 100 * POWER(1 + yi.int_rate, year_counts.year_count) - 100 AS curr_yr_int
) AS curr_yr
CROSS APPLY (
SELECT curr_yr.curr_yr_int - (100 * POWER(1 + yi.int_rate, (year_counts.year_count - 1)) - 100) AS prev_yr_int
) AS prev_yr
WHERE
yi.year >= @start_year
ORDER BY yi.year
0

LVL 59

Assisted Solution

Kevin Cross earned 167 total points
One solution is to use a common table expression.

``````WITH cte(Yr, Pct, PctOfOriginal, TotalPct) AS (
SELECT Yr
, CONVERT(DECIMAL(10,8), Pct)
, CONVERT(DECIMAL(10,8), Pct)
, CONVERT(DECIMAL(10,8), Pct)
FROM your_table
WHERE Yr = 2009

UNION ALL

SELECT nxt.Yr
, CONVERT(DECIMAL(10,8), nxt.Pct)
, CONVERT(DECIMAL(10,8), (1+prv.TotalPct)*(1+nxt.Pct)-(1+prv.TotalPct))
, CONVERT(DECIMAL(10,8), (1+prv.TotalPct)*(1+nxt.Pct)-1)
FROM cte prv
JOIN your_table nxt ON nxt.Yr = prv.Yr+1
WHERE nxt.Yr <= 2013
)
SELECT *
FROM cte
;
``````

The idea is to step through the small list of years and use values from previous calc.  You could do this with subqueries also, but thought the CTE approach was a little more manageable.  You may not need the CONVERT code, but recursive query needs same data type in the anchor and recursive queries.

In SQL 2012, you may be able to leverage the enhanced features of the OVER() analytic clause with functions like SUM() as well as the LEAD() and LAG() functions.
0

LVL 59

Expert Comment

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.
0

LVL 31

Expert Comment

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
;
0

LVL 31

Assisted Solution

awking00 earned 166 total points
If 5% is stored as 5 -
select x.year, x.rate, power(1 + (x.rate/100),rn - 1) * x.rate as newrate, 100 * (power(1 + (x.rate/100), x.rn) - 1) as cum_rate from
(select year, rate, row_number() over (order by year) rn
from inflation_rate) as x
;
0

LVL 3

Author Comment

Trying to understand CROSS_APPLY... What does it do?
0

LVL 59

Expert Comment

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.
0

LVL 3

Author Closing Comment

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

Featured Post

Suggested Solutions

Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.