Updated: 30 September 2010

*Note: This documentation is for the SQL2008 (and later) version of this XLeratorDB function, it is not compatible with SQL Server 2005.*

Click here for the SQL2005 version of the GROWTH function

Use the multi-input aggregate GROWTH to calculate predicted exponential growth by using existing data. GROWTH returns the y-value for a new x-value that you specify using existing x-values and y-values.

Where

a = 10^{m}

b = SLOPE(log(known_y) / log(10), known_x)

m = INTERCEPT(log(known_y) / log(10), known_x)

t = new x

SELECT [wctStatistics].[wct].[GROWTH] (

,<@Known_y, float,>

,<@Known_x, float,>

,<@new_x, float,>)

the y-values to be used in the GROWTH calculation. *@Known_y* is an expression of type **float** or of a type that can be implicitly converted to **float**.

the x-values to be used in the GROWTH calculation. *@Known_x* is an expression of type **float** or of a type that can be implicitly converted to **float**.

the new x-value for which you want GROWTH to calculate the y-value. *@New_x* is an expression of type **float** or of a type that can be implicitly converted to **float**.

float

· If any of the values in the known-y dataset is less than or equal to zero, GROWTH returns a NULL

· GROWTH is an AGGREGATE function and follows the same conventions as all other AGGREGATE functions in SQL Server.

In this example, we calculate the growth for a single set of x- and y-values with a single new x value

SELECT wct.GROWTH(y, x, 8) as GROWTH

FROM (

SELECT 1, 1804 UNION ALL

SELECT 2, 1927 UNION ALL

SELECT 3, 1960 UNION ALL

SELECT 4, 1974 UNION ALL

SELECT 5, 1987 UNION ALL

SELECT 6, 1999 UNION ALL

SELECT 7, 2011

) n(x,y)

This produces the following result

GROWTH

----------------------

2069.10299555407

(1 row(s) affected)

In this example, we will populate some temporary table with some historical population information and then calculate the growth. First, create the table and put some data in it:

CREATE TABLE #p(

continent nvarchar(50),

year bigint,

pop bigint

)

INSERT INTO #p VALUES ('Africa',1950,227270)

INSERT INTO #p VALUES ('Asia',1950,1402887)

INSERT INTO #p VALUES ('Europe',1950,547460)

INSERT INTO #p VALUES ('Latin America and the Caribbean',1950,167307)

INSERT INTO #p VALUES ('Northern America',1950,171615)

INSERT INTO #p VALUES ('Oceania',1950,12807)

INSERT INTO #p VALUES ('Africa',1975,418765)

INSERT INTO #p VALUES ('Asia',1975,2379374)

INSERT INTO #p VALUES ('Europe',1975,676207)

INSERT INTO #p VALUES ('Latin America and the Caribbean',1975,323323)

INSERT INTO #p VALUES ('Northern America',1975,242360)

INSERT INTO #p VALUES ('Oceania',1975,21286)

INSERT INTO #p VALUES ('Africa',2000,819462)

INSERT INTO #p VALUES ('Asia',2000,3698296)

INSERT INTO #p VALUES ('Europe',2000,726568)

INSERT INTO #p VALUES ('Latin America and the Caribbean',2000,521228)

INSERT INTO #p VALUES ('Northern America',2000,318654)

INSERT INTO #p VALUES ('Oceania',2000,31160)

INSERT INTO #p VALUES ('Africa',2010,1033043)

INSERT INTO #p VALUES ('Asia',2010,4166741)

INSERT INTO #p VALUES ('Europe',2010,732759)

INSERT INTO #p VALUES ('Latin America and the Caribbean',2010,588649)

INSERT INTO #p VALUES ('Northern America',2010,351659)

INSERT INTO #p VALUES ('Oceania',2010,35838)

In this example, we will calculate the population by continent in the year 2025

SELECT continent

,ROUND(wct.GROWTH(pop, year, 2025), 0) as GROWTH

FROM #p

GROUP BY continent

This produces the following result.

continent GROWTH

-------------------------------------------------- ----------------------

Africa 1523464

Asia 5698985

Europe 812097

Latin America and the Caribbean 856450

Northern America 427360

Oceania 47493

(6 row(s) affected)