GROWTH
Updated 2023-11-01 10:46:09.100000
Syntax
SELECT [westclintech].[wct].[GROWTH] (
,<@Known_y, float,>
,<@Known_x, float,>
,<@new_x, float,>)
Description
Use the aggregate function 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.
GROWTH = ar^{t}
Wherea = 10mr = 10bb = SLOPE(log(known_y) / log(10), known_x)m = INTERCEPT(log(known_y) / log(10), known_x)t = new x
Arguments
@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.
@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.
Return Type
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.
{"columns":[{"field":"GROWTH","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"GROWTH":"2069.10299555407"}]}
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.
{"columns":[{"field":"continent"},{"field":"GROWTH","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"continent":"Africa","GROWTH":"1523464"},{"continent":"Asia","GROWTH":"5698985"},{"continent":"Europe","GROWTH":"812097"},{"continent":"Latin America and the Caribbean","GROWTH":"856450"},{"continent":"Northern America","GROWTH":"427360"},{"continent":"Oceania","GROWTH":"47493"}]}