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.
vegasdevAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Patrick MatthewsConnect With a Mentor 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

*/

Open in new window

0
All Courses

From novice to tech pro — start learning today.