Login     Register

        Contact Us     Search

XLeratorDB/statistics Documentation

SQL Server GROWTH function


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.
 
GROWTH function for SQL Server
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)



Copyright 2008-2013 WestClinTech LLC         Privacy Policy        Terms of Service