GROWTH
Updated: 15 January 2011
Use the 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), known_x)
m = INTERCEPT(log(known_y. known_x)
t = new x
Syntax
SELECT [wctStatistics].[wct].[GROWTH] (
,<@fmtGROWTH, nvarchar(4000),>)
The syntax has changed as of release 1.07 and the function has changed from a scalar to an aggregate. Please make the appropriate changes when you upgrade to 1.07.
Arguments
@fmtGROWTH
An nvarchar string formatted by the fmtGROWTH function containing the known-y and known-x and new-x values to be used by the GROWTH calculation.
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.
· GROWTH is a one-pass solution. For a two-pass solution, use the GROWTH_q function.
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(wct.fmtGROWTH(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(wct.fmtGROWTH(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)