Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


Calculate Inflation

Posted on 2013-12-18
Medium Priority
Last Modified: 2014-01-11
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
Question by:bvRocks
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
LVL 70

Accepted Solution

Scott Pletcher earned 668 total points
ID: 39727754
DECLARE @start_year int
SET @start_year = 2009

    yi.year, yi.int_rate * 100 AS int_rate, prev_yr.prev_yr_int, curr_yr.curr_yr_int
    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
    SELECT yi.year - @start_year + 1 AS year_count
) AS year_counts
    SELECT 100 * POWER(1 + yi.int_rate, year_counts.year_count) - 100 AS curr_yr_int
) AS curr_yr
    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
    yi.year >= @start_year
ORDER BY yi.year
LVL 60

Assisted Solution

by:Kevin Cross
Kevin Cross earned 668 total points
ID: 39727758
One solution is to use a common table expression.

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


    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
FROM cte

Open in new window

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.
LVL 60

Expert Comment

by:Kevin Cross
ID: 39727764
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.
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

LVL 32

Expert Comment

ID: 39732294
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
LVL 32

Assisted Solution

awking00 earned 664 total points
ID: 39732305
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

Author Comment

ID: 39739728
Trying to understand CROSS_APPLY... What does it do?
LVL 60

Expert Comment

by:Kevin Cross
ID: 39739927
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.

Author Closing Comment

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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

609 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question