Login     Register

        Contact Us     Search

XLeratorDB/statistics Documentation

SQL Server TREND function


TREND

Updated: 24 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 TREND function


Use the multi-input aggregate TREND function to calculate the values along a linear trend. TREND fits a straight line (using the method of least squares) to the known-y dataset and the known-x dataset. The equation for TREND is:
 
TREND function for SQL Server 
 
 
Then 
TREND = (m * @new_x) + b
Syntax
SELECT [wctStatistics].[wct].[TREND] (
 ,<@Known_y, float,>
 ,<@Known_x, float,>
 ,<@new_x, float,>)
Arguments
 
@Known_y
the y-values to be used in the TREND 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 TREND 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 TREND 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
·         TREND is an AGGREGATE function and follows the same conventions as all other AGGREGATE functions in SQL Server.
Examples
In this example, we calculate the trend for a single set of x- and y-values with a single new x value
SELECT wct.TREND(y, x, 12.5) as TREND
FROM (
      SELECT 0.75, 1 UNION ALL
      SELECT 2.5, 2 UNION ALL
      SELECT 6.75, 3 UNION ALL
      SELECT 10, 4
      ) n(x,y)
 
This produces the following result
TREND
----------------------
4.79116945107399
 
(1 row(s) affected)
In this example, we will populate some temporary table with some historical financial information and then calculate the slope. First, create the table and put some data in it:
CREATE TABLE #c(
      SYM         NVARCHAR(5),
      YE          BIGINT,
      REV         FLOAT,
      GPROF       FLOAT,
      OPINC       FLOAT,
      NETINC      FLOAT
      )
     
INSERT INTO #c VALUES('YHOO',2009,6460.32,3588.57,386.69,597.99)
INSERT INTO #c VALUES('YHOO',20,72.5,4185.14,12.96,418.92)
INSERT INTO #c VALUES('YHOO',2007,6969.27,4130.52,695.41,639.16)
INSERT INTO #c VALUES('YHOO',2006,6425.68,3749.96,940.97,751.39)
INSERT INTO #c VALUES('YHOO',2005,5257.67,3161.47,1107.73,1896.23)
INSERT INTO #c VALUES('GOOG',2009,23650.56,14806.45,8312.19,6520.45)
INSERT INTO #c VALUES('GOOG',20,21795.55,13174.04,5537.21,4226.86)
INSERT INTO #c VALUES('GOOG',2007,16593.99,9944.9,54.44,4203.72)
INSERT INTO #c VALUES('GOOG',2006,10604.92,6379.89,3550,3077.45)
INSERT INTO #c VALUES('GOOG',2005,6138.56,3561.47,2017.28,1465.4)
INSERT INTO #c VALUES('MSFT',2009,62484,509,24167,18760)
INSERT INTO #c VALUES('MSFT',20,58437,46282,21225,14569)
INSERT INTO #c VALUES('MSFT',2007,60420,48822,22271,17681)
INSERT INTO #c VALUES('MSFT',2006,51122,40429,18438,14065)
INSERT INTO #c VALUES('MSFT',2005,44282,36632,16064,12599)
INSERT INTO #c VALUES('ORCL',2009,26820,21056,9062,6135)
INSERT INTO #c VALUES('ORCL',20,23252,18458,8321,5593)
INSERT INTO #c VALUES('ORCL',2007,22430,17449,7844,5521)
INSERT INTO #c VALUES('ORCL',2006,17996,13805,5974,4274)
INSERT INTO #c VALUES('ORCL',2005,14380,11145,4736,3381)
INSERT INTO #c VALUES('SAP',2009,10672,6980,2588,1748)
INSERT INTO #c VALUES('SAP',20,11575,7370,2701,1847)
INSERT INTO #c VALUES('SAP',2007,10256,6631,2698,1906)
INSERT INTO #c VALUES('SAP',2006,9393,6064,2578,1871)
INSERT INTO #c VALUES('SAP',2005,8509,5460,2337,1496)
In this example, we will calculate the trend of the revenue (REV) against the year (YE)
SELECT SYM
,ROUND(wct.TREND(REV,YE,2010), 0) as [2010 Revnue]   
,ROUND(wct.TREND(REV,YE,2011), 0) as [2011 Revnue]   
,ROUND(wct.TREND(REV,YE,2012), 0) as [2012 Revnue]   
FROM #c
GROUP BY SYM
This produces the following result.
SYM   2010 Revnue            2011 Revnue            2012 Revnue
----- ---------------------- ---------------------- ----------------------
GOOG 29621                  34243                  38864
MSFT 68465                  72837                  772
ORCL 30016                  33030                  36044
SAP  12033                  12684                  13335
YHOO 7421                   7740                   8058
 
(5 row(s) affected)
To calculate the net income using the revenue projections from the above query, we could enter the following statement.
SELECT #c.SYM
,ROUND(wct.TREND(NETINC,REV,[2010 Revenue]), 0) as [2010 Net Income]   
,ROUND(wct.TREND(NETINC,REV,[2011 Revenue]), 0) as [2011 Net Income]   
,ROUND(wct.TREND(NETINC,REV,[2012 Revenue]), 0) as [2012 Net Income]   
FROM (
      SELECT SYM
      ,ROUND(wct.TREND(REV,YE,2010), 0) as [2010 Revenue]  
      ,ROUND(wct.TREND(REV,YE,2011), 0) as [2011 Revenue]  
      ,ROUND(wct.TREND(REV,YE,2012), 0) as [2012 Revenue]  
      FROM #c
      GROUP BY SYM) n, #c
WHERE n.sym = #c.sym
GROUP BY #c.SYM
This returns the following results.
SYM   2010 Net Income        2011 Net Income        2012 Net Income
----- ---------------------- ---------------------- ----------------------
GOOG 7107                   8176                   9246
MSFT 19566                  20910                  22253
ORCL 7060                   7753                   8447
SAP  1945                   2002                   2059
YHOO 149                    -88                    -324
 
(5 row(s) affected)
As the following query demonstrates, this returns a different result than if we had just looked at the net income over time.
SELECT #c.SYM
,ROUND(wct.TREND(NETINC,YE,2010), 0) as [2010 Net Income]  
,ROUND(wct.TREND(NETINC,YE,2011), 0) as [2011 Net Income]  
,ROUND(wct.TREND(NETINC,YE,2012), 0) as [2012 Net Income]  
FROM #c
GROUP BY SYM
This produces the following result.
SYM   2010 Net Income        2011 Net Income        2012 Net Income
----- ---------------------- ---------------------- ----------------------
GOOG 7277                   8403                   9529
MSFT 19383                  20665                  21948
ORCL 7029                   7712                   8394
SAP  1918                   1966                   2014
YHOO -18                    -311                   -604
 

(5 row(s) affected)

See Also


Copyright 2008-2013 WestClinTech LLC         Privacy Policy        Terms of Service