GROWTH
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 = 10m
r = 10b
b = SLOPE(log(known_y) / log(10), known_x)
m = INTERCEPT(log(known_y) / log(10), known_x)
t = new x
Syntax
SELECT [wctStatistics].[wct].[GROWTH] (
,<@Known_y, float,>
,<@Known_x, float,>
,<@new_x, float,>)
Arguments
@Known_y
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.
@Known_x
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.
@New_x
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.
Return Types
float
Remarks
· 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.
Examples
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)