# SQL Compound Growth Rate Calcuations

Hello,

I'm working on a graph for a client that needs to show growth rates for a population.  I have the starting population and the date of that starting population.  I know the annual growth rate is 30%.  What I need is to take that growth and be able to calculate the growth of the population for a given date in the future.

I.e. Starting Population 100, Growth Rate 30%, Start Date 1/1/2010
Population on 1/1/2011 would be 130.

How would I calculate the population of the dataset say for 6/1/2010?
Or 6/1/2013 ?

MS SQL Function, Stored Procedure, etc

Thank you.
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
Cheating a little, and assuming that each month is of the same length, this code seems to be doing the trick:

``````CREATE PROCEDURE dbo.GetPopulation (@StartDate datetime, @Months int, @StartPop numeric(20, 6),
@AnnualGrowth numeric(20, 6))

AS BEGIN

DECLARE @tbl TABLE (AsOfDate datetime, Population numeric(15, 2))
DECLARE @MonthRate numeric(20, 6)
DECLARE @MonthCount int
DECLARE @Mult numeric(20, 6)

SET @MonthRate = POWER(1 + @AnnualGrowth, 1 / 12.0)
SET @MonthCount = 0

WHILE @MonthCount < @Months BEGIN
SET @Mult = POWER(@MonthRate, @MonthCount)
INSERT INTO @tbl (AsOfDate, Population) VALUES (DATEADD(m, @MonthCount, @StartDate), @StartPop * @Mult)
SET @MonthCount = @MonthCount + 1
END

SELECT * FROM @Tbl

END

GO

EXEC dbo.GetPopulation '2010-01-01', 24, 100, 0.3;

GO

/* That code returns:

AsOfDate                Population
----------------------------------
2010-01-01 00:00:00.000	100.00
2010-02-01 00:00:00.000	102.21
2010-03-01 00:00:00.000	104.47
2010-04-01 00:00:00.000	106.78
2010-05-01 00:00:00.000	109.14
2010-06-01 00:00:00.000	111.55
2010-07-01 00:00:00.000	114.02
2010-08-01 00:00:00.000	116.54
2010-09-01 00:00:00.000	119.11
2010-10-01 00:00:00.000	121.75
2010-11-01 00:00:00.000	124.44
2010-12-01 00:00:00.000	127.19
2011-01-01 00:00:00.000	130.00
2011-02-01 00:00:00.000	132.87
2011-03-01 00:00:00.000	135.81
2011-04-01 00:00:00.000	138.81
2011-05-01 00:00:00.000	141.88
2011-06-01 00:00:00.000	145.02
2011-07-01 00:00:00.000	148.22
2011-08-01 00:00:00.000	151.50
2011-09-01 00:00:00.000	154.85
2011-10-01 00:00:00.000	158.27
2011-11-01 00:00:00.000	161.77
2011-12-01 00:00:00.000	165.34

*/
``````
0

Experts Exchange Solution brought to you by