TREND

Updated 2023-11-02 13:09:02.757000

Syntax

SELECT [westclintech].[wct].[TREND] (
  <@Known_y, float,>
 ,<@Known_x, float,>
 ,<@new_x, float,>)

Description

Use the aggregate function TREND 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:

 \\ \bar{a} = \bar{y} - b\bar{x} \\ \text{where the slope, }b\text{, is calculated as:} \\ b = \frac{\sum(x-\bar{x})(y-\bar{y})}{\sum(x-\bar{x})^2

ThenTREND = (m * @new_x) + b

Arguments

@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.

@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.

Return Type

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.

{"columns":[{"field":"TREND","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"TREND":"4.79116945107399"}]}

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', 2008, 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', 2008, 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', 2008, 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', 2008, 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', 2008, 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 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;

This produces the following result.

{"columns":[{"field":"SYM"},{"field":"2010 Revenue","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"2011 Revenue","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"2012 Revenue","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"SYM":"GOOG","2010 Revenue":"29621","2011 Revenue":"34243","2012 Revenue":"38864"},{"SYM":"MSFT","2010 Revenue":"68465","2011 Revenue":"72837","2012 Revenue":"772"},{"SYM":"ORCL","2010 Revenue":"30016","2011 Revenue":"33030","2012 Revenue":"36044"},{"SYM":"SAP","2010 Revenue":"12033","2011 Revenue":"12684","2012 Revenue":"13335"},{"SYM":"YHOO","2010 Revenue":"3853","2011 Revenue":"3458","2012 Revenue":"3063"}]}

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.

{"columns":[{"field":"SYM"},{"field":"2010 Net Income","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"2011 Net Income","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"2012 Net Income","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"SYM":"GOOG","2010 Net Income":"7107","2011 Net Income":"8176","2012 Net Income":"9246"},{"SYM":"MSFT","2010 Net Income":"19566","2011 Net Income":"20910","2012 Net Income":"22253"},{"SYM":"ORCL","2010 Net Income":"7060","2011 Net Income":"7753","2012 Net Income":"8447"},{"SYM":"SAP","2010 Net Income":"1945","2011 Net Income":"2002","2012 Net Income":"2059"},{"SYM":"YHOO","2010 Net Income":"807","2011 Net Income":"789","2012 Net Income":"771"}]}

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.

{"columns":[{"field":"SYM"},{"field":"2010 Net Income","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"2011 Net Income","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"2012 Net Income","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"SYM":"GOOG","2010 Net Income":"7277","2011 Net Income":"8403","2012 Net Income":"9529"},{"SYM":"MSFT","2010 Net Income":"19383","2011 Net Income":"20665","2012 Net Income":"21948"},{"SYM":"ORCL","2010 Net Income":"7029","2011 Net Income":"7712","2012 Net Income":"8394"},{"SYM":"SAP","2010 Net Income":"1918","2011 Net Income":"1966","2012 Net Income":"2014"},{"SYM":"YHOO","2010 Net Income":"-18","2011 Net Income":"-311","2012 Net Income":"-604"}]}

TREND

Syntax

Description

Arguments

@Known_x

@new_x

@Known_y

Return Type

Remarks

Examples

See Also