TREND_q
Updated 2023-11-02 13:13:44.313000
Syntax
SELECT [westclintech].[wct].[TREND_q] (
<@Known_y_Known_x_RangeQuery, nvarchar(4000),>
,<@New_x, float,>)
Description
Use the scalar function TREND_q to calculate the values along a linear trend. TREND_q fits a straight line (using the method of least squares) to the known-y dataset and the known-x dataset. The equation for TREND_q 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_y_Known_x_RangeQuery
the select statement, as text, used to determine the known y- and x-values to be used in this function.
@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 Type
float
Remarks
If the number of known-y data points is not equal to the number of known-x data points, TREND_q will return an error.
No GROUP BY is required for this function even though it produces aggregated results.
Examples
Using the normalized table #f1, calculate the sales trend for GOOG in 2009, based on data subsequent to the 2008 fiscal year.
CREATE TABLE #f1
(
[SYMBOL] [nvarchar](10) NOT NULL,
[FY] [float] NOT NULL,
[DESCR] [nvarchar](30) NOT NULL,
[AMOUNT] [float] NOT NULL,
);
INSERT INTO #f1
VALUES
('GOOG', 2008, 'Sales', 21795.55);
INSERT INTO #f1
VALUES
('GOOG', 2007, 'Sales', 16593.99);
INSERT INTO #f1
VALUES
('GOOG', 2006, 'Sales', 10604.92);
INSERT INTO #f1
VALUES
('GOOG', 2005, 'Sales', 6138.56);
INSERT INTO #f1
VALUES
('GOOG', 2004, 'Sales', 3189.22);
INSERT INTO #f1
VALUES
('GOOG', 2003, 'Sales', 1465.93);
INSERT INTO #f1
VALUES
('GOOG', 2002, 'Sales', 439.51);
INSERT INTO #f1
VALUES
('GOOG', 2001, 'Sales', 86.43);
INSERT INTO #f1
VALUES
('GOOG', 2000, 'Sales', 19.11);
INSERT INTO #f1
VALUES
('GOOG', 1999, 'Sales', 0.22);
INSERT INTO #f1
VALUES
('EBAY', 2008, 'Sales', 8541.26);
INSERT INTO #f1
VALUES
('EBAY', 2007, 'Sales', 7672.33);
INSERT INTO #f1
VALUES
('EBAY', 2006, 'Sales', 5969.74);
INSERT INTO #f1
VALUES
('EBAY', 2005, 'Sales', 4552.4);
INSERT INTO #f1
VALUES
('EBAY', 2004, 'Sales', 3271.31);
INSERT INTO #f1
VALUES
('EBAY', 2003, 'Sales', 2165.1);
INSERT INTO #f1
VALUES
('EBAY', 2002, 'Sales', 1214.1);
INSERT INTO #f1
VALUES
('EBAY', 2001, 'Sales', 748.82);
INSERT INTO #f1
VALUES
('EBAY', 2000, 'Sales', 431.42);
INSERT INTO #f1
VALUES
('EBAY', 1999, 'Sales', 224.72);
INSERT INTO #f1
VALUES
('AMZN', 2008, 'Sales', 19166);
INSERT INTO #f1
VALUES
('AMZN', 2007, 'Sales', 14835);
INSERT INTO #f1
VALUES
('AMZN', 2006, 'Sales', 10711);
INSERT INTO #f1
VALUES
('AMZN', 2005, 'Sales', 8490);
INSERT INTO #f1
VALUES
('AMZN', 2004, 'Sales', 6921.12);
INSERT INTO #f1
VALUES
('AMZN', 2003, 'Sales', 5263.7);
INSERT INTO #f1
VALUES
('AMZN', 2002, 'Sales', 3932.94);
INSERT INTO #f1
VALUES
('AMZN', 2001, 'Sales', 3122.43);
INSERT INTO #f1
VALUES
('AMZN', 2000, 'Sales', 2761.98);
INSERT INTO #f1
VALUES
('AMZN', 1999, 'Sales', 1639.84);
INSERT INTO #f1
VALUES
('CSCO', 2008, 'Sales', 39540);
INSERT INTO #f1
VALUES
('CSCO', 2007, 'Sales', 34922);
INSERT INTO #f1
VALUES
('CSCO', 2006, 'Sales', 28484);
INSERT INTO #f1
VALUES
('CSCO', 2005, 'Sales', 24801);
INSERT INTO #f1
VALUES
('CSCO', 2004, 'Sales', 22045);
INSERT INTO #f1
VALUES
('CSCO', 2003, 'Sales', 18878);
INSERT INTO #f1
VALUES
('CSCO', 2002, 'Sales', 18915);
INSERT INTO #f1
VALUES
('CSCO', 2001, 'Sales', 22293);
INSERT INTO #f1
VALUES
('CSCO', 2000, 'Sales', 18928);
INSERT INTO #f1
VALUES
('CSCO', 1999, 'Sales', 12173);
INSERT INTO #f1
VALUES
('MSFT', 2008, 'Sales', 60420);
INSERT INTO #f1
VALUES
('MSFT', 2007, 'Sales', 51122);
INSERT INTO #f1
VALUES
('MSFT', 2006, 'Sales', 44282);
INSERT INTO #f1
VALUES
('MSFT', 2005, 'Sales', 39788);
INSERT INTO #f1
VALUES
('MSFT', 2004, 'Sales', 36835);
INSERT INTO #f1
VALUES
('MSFT', 2003, 'Sales', 32187);
INSERT INTO #f1
VALUES
('MSFT', 2002, 'Sales', 28365);
INSERT INTO #f1
VALUES
('MSFT', 2001, 'Sales', 25296);
INSERT INTO #f1
VALUES
('MSFT', 2000, 'Sales', 22956);
INSERT INTO #f1
VALUES
('MSFT', 1999, 'Sales', 19747);
INSERT INTO #f1
VALUES
('GOOG', 2008, 'EBIT', 5853.6);
INSERT INTO #f1
VALUES
('GOOG', 2007, 'EBIT', 5673.98);
INSERT INTO #f1
VALUES
('GOOG', 2006, 'EBIT', 4011.04);
INSERT INTO #f1
VALUES
('GOOG', 2005, 'EBIT', 2141.68);
INSERT INTO #f1
VALUES
('GOOG', 2004, 'EBIT', 650.23);
INSERT INTO #f1
VALUES
('GOOG', 2003, 'EBIT', 346.65);
INSERT INTO #f1
VALUES
('GOOG', 2002, 'EBIT', 184.92);
INSERT INTO #f1
VALUES
('GOOG', 2001, 'EBIT', 10.07);
INSERT INTO #f1
VALUES
('GOOG', 2000, 'EBIT', -14.69);
INSERT INTO #f1
VALUES
('GOOG', 1999, 'EBIT', -6.08);
INSERT INTO #f1
VALUES
('EBAY', 2008, 'EBIT', 2183.56);
INSERT INTO #f1
VALUES
('EBAY', 2007, 'EBIT', 750.85);
INSERT INTO #f1
VALUES
('EBAY', 2006, 'EBIT', 1547.06);
INSERT INTO #f1
VALUES
('EBAY', 2005, 'EBIT', 1549.33);
INSERT INTO #f1
VALUES
('EBAY', 2004, 'EBIT', 1128.23);
INSERT INTO #f1
VALUES
('EBAY', 2003, 'EBIT', 661.5);
INSERT INTO #f1
VALUES
('EBAY', 2002, 'EBIT', 398.13);
INSERT INTO #f1
VALUES
('EBAY', 2001, 'EBIT', 162.94);
INSERT INTO #f1
VALUES
('EBAY', 2000, 'EBIT', 77.96);
INSERT INTO #f1
VALUES
('EBAY', 1999, 'EBIT', 18.14);
INSERT INTO #f1
VALUES
('AMZN', 2008, 'EBIT', 901);
INSERT INTO #f1
VALUES
('AMZN', 2007, 'EBIT', 660);
INSERT INTO #f1
VALUES
('AMZN', 2006, 'EBIT', 377);
INSERT INTO #f1
VALUES
('AMZN', 2005, 'EBIT', 428);
INSERT INTO #f1
VALUES
('AMZN', 2004, 'EBIT', 355.87);
INSERT INTO #f1
VALUES
('AMZN', 2003, 'EBIT', 38.99);
INSERT INTO #f1
VALUES
('AMZN', 2002, 'EBIT', -150.63);
INSERT INTO #f1
VALUES
('AMZN', 2001, 'EBIT', -526.43);
INSERT INTO #f1
VALUES
('AMZN', 2000, 'EBIT', -1106.68);
INSERT INTO #f1
VALUES
('AMZN', 1999, 'EBIT', -643.2);
INSERT INTO #f1
VALUES
('CSCO', 2008, 'EBIT', 10255);
INSERT INTO #f1
VALUES
('CSCO', 2007, 'EBIT', 9461);
INSERT INTO #f1
VALUES
('CSCO', 2006, 'EBIT', 7633);
INSERT INTO #f1
VALUES
('CSCO', 2005, 'EBIT', 8036);
INSERT INTO #f1
VALUES
('CSCO', 2004, 'EBIT', 6992);
INSERT INTO #f1
VALUES
('CSCO', 2003, 'EBIT', 5013);
INSERT INTO #f1
VALUES
('CSCO', 2002, 'EBIT', 2710);
INSERT INTO #f1
VALUES
('CSCO', 2001, 'EBIT', -874);
INSERT INTO #f1
VALUES
('CSCO', 2000, 'EBIT', 4343);
INSERT INTO #f1
VALUES
('CSCO', 1999, 'EBIT', 3203);
INSERT INTO #f1
VALUES
('MSFT', 2008, 'EBIT', 23814);
INSERT INTO #f1
VALUES
('MSFT', 2007, 'EBIT', 20101);
INSERT INTO #f1
VALUES
('MSFT', 2006, 'EBIT', 18262);
INSERT INTO #f1
VALUES
('MSFT', 2005, 'EBIT', 16628);
INSERT INTO #f1
VALUES
('MSFT', 2004, 'EBIT', 12196);
INSERT INTO #f1
VALUES
('MSFT', 2003, 'EBIT', 11054);
INSERT INTO #f1
VALUES
('MSFT', 2002, 'EBIT', 7875);
INSERT INTO #f1
VALUES
('MSFT', 2001, 'EBIT', 11525);
INSERT INTO #f1
VALUES
('MSFT', 2000, 'EBIT', 14275);
INSERT INTO #f1
VALUES
('MSFT', 1999, 'EBIT', 11891);
INSERT INTO #f1
VALUES
('GOOG', 2008, 'Depreciation', 1499.89);
INSERT INTO #f1
VALUES
('GOOG', 2007, 'Depreciation', 967.66);
INSERT INTO #f1
VALUES
('GOOG', 2006, 'Depreciation', 571.94);
INSERT INTO #f1
VALUES
('GOOG', 2005, 'Depreciation', 293.81);
INSERT INTO #f1
VALUES
('GOOG', 2004, 'Depreciation', 148.47);
INSERT INTO #f1
VALUES
('GOOG', 2003, 'Depreciation', 50.19);
INSERT INTO #f1
VALUES
('GOOG', 2002, 'Depreciation', 18.03);
INSERT INTO #f1
VALUES
('GOOG', 2001, 'Depreciation', 10.03);
INSERT INTO #f1
VALUES
('GOOG', 2000, 'Depreciation', 0);
INSERT INTO #f1
VALUES
('GOOG', 1999, 'Depreciation', 0);
INSERT INTO #f1
VALUES
('EBAY', 2008, 'Depreciation', 719.81);
INSERT INTO #f1
VALUES
('EBAY', 2007, 'Depreciation', 576.61);
INSERT INTO #f1
VALUES
('EBAY', 2006, 'Depreciation', 521.63);
INSERT INTO #f1
VALUES
('EBAY', 2005, 'Depreciation', 369.54);
INSERT INTO #f1
VALUES
('EBAY', 2004, 'Depreciation', 249.42);
INSERT INTO #f1
VALUES
('EBAY', 2003, 'Depreciation', 156.46);
INSERT INTO #f1
VALUES
('EBAY', 2002, 'Depreciation', 76.64);
INSERT INTO #f1
VALUES
('EBAY', 2001, 'Depreciation', 78.39);
INSERT INTO #f1
VALUES
('EBAY', 2000, 'Depreciation', 38.13);
INSERT INTO #f1
VALUES
('EBAY', 1999, 'Depreciation', 19.75);
INSERT INTO #f1
VALUES
('AMZN', 2008, 'Depreciation', 340);
INSERT INTO #f1
VALUES
('AMZN', 2007, 'Depreciation', 271);
INSERT INTO #f1
VALUES
('AMZN', 2006, 'Depreciation', 210);
INSERT INTO #f1
VALUES
('AMZN', 2005, 'Depreciation', 118);
INSERT INTO #f1
VALUES
('AMZN', 2004, 'Depreciation', 75.66);
INSERT INTO #f1
VALUES
('AMZN', 2003, 'Depreciation', 72.74);
INSERT INTO #f1
VALUES
('AMZN', 2002, 'Depreciation', 81.66);
INSERT INTO #f1
VALUES
('AMZN', 2001, 'Depreciation', 264.03);
INSERT INTO #f1
VALUES
('AMZN', 2000, 'Depreciation', 404.78);
INSERT INTO #f1
VALUES
('AMZN', 1999, 'Depreciation', 251.5);
INSERT INTO #f1
VALUES
('CSCO', 2008, 'Depreciation', 1977);
INSERT INTO #f1
VALUES
('CSCO', 2007, 'Depreciation', 1569);
INSERT INTO #f1
VALUES
('CSCO', 2006, 'Depreciation', 1353);
INSERT INTO #f1
VALUES
('CSCO', 2005, 'Depreciation', 1020);
INSERT INTO #f1
VALUES
('CSCO', 2004, 'Depreciation', 1199);
INSERT INTO #f1
VALUES
('CSCO', 2003, 'Depreciation', 1463);
INSERT INTO #f1
VALUES
('CSCO', 2002, 'Depreciation', 1957);
INSERT INTO #f1
VALUES
('CSCO', 2001, 'Depreciation', 2236);
INSERT INTO #f1
VALUES
('CSCO', 2000, 'Depreciation', 863);
INSERT INTO #f1
VALUES
('CSCO', 1999, 'Depreciation', 489);
INSERT INTO #f1
VALUES
('MSFT', 2008, 'Depreciation', 1872);
INSERT INTO #f1
VALUES
('MSFT', 2007, 'Depreciation', 1406);
INSERT INTO #f1
VALUES
('MSFT', 2006, 'Depreciation', 990);
INSERT INTO #f1
VALUES
('MSFT', 2005, 'Depreciation', 884);
INSERT INTO #f1
VALUES
('MSFT', 2004, 'Depreciation', 817);
INSERT INTO #f1
VALUES
('MSFT', 2003, 'Depreciation', 1090);
INSERT INTO #f1
VALUES
('MSFT', 2002, 'Depreciation', 1014);
INSERT INTO #f1
VALUES
('MSFT', 2001, 'Depreciation', 1238);
INSERT INTO #f1
VALUES
('MSFT', 2000, 'Depreciation', 900);
INSERT INTO #f1
VALUES
('MSFT', 1999, 'Depreciation', 483);
INSERT INTO #f1
VALUES
('GOOG', 2008, 'Net Income', 4226.86);
INSERT INTO #f1
VALUES
('GOOG', 2007, 'Net Income', 4203.72);
INSERT INTO #f1
VALUES
('GOOG', 2006, 'Net Income', 3077.45);
INSERT INTO #f1
VALUES
('GOOG', 2005, 'Net Income', 1465.4);
INSERT INTO #f1
VALUES
('GOOG', 2004, 'Net Income', 399.12);
INSERT INTO #f1
VALUES
('GOOG', 2003, 'Net Income', 105.65);
INSERT INTO #f1
VALUES
('GOOG', 2002, 'Net Income', 99.66);
INSERT INTO #f1
VALUES
('GOOG', 2001, 'Net Income', 6.99);
INSERT INTO #f1
VALUES
('GOOG', 2000, 'Net Income', -14.69);
INSERT INTO #f1
VALUES
('GOOG', 1999, 'Net Income', -6.08);
INSERT INTO #f1
VALUES
('EBAY', 2008, 'Net Income', 1779.47);
INSERT INTO #f1
VALUES
('EBAY', 2007, 'Net Income', 348.25);
INSERT INTO #f1
VALUES
('EBAY', 2006, 'Net Income', 1125.64);
INSERT INTO #f1
VALUES
('EBAY', 2005, 'Net Income', 1082.04);
INSERT INTO #f1
VALUES
('EBAY', 2004, 'Net Income', 778.22);
INSERT INTO #f1
VALUES
('EBAY', 2003, 'Net Income', 447.18);
INSERT INTO #f1
VALUES
('EBAY', 2002, 'Net Income', 249.89);
INSERT INTO #f1
VALUES
('EBAY', 2001, 'Net Income', 90.45);
INSERT INTO #f1
VALUES
('EBAY', 2000, 'Net Income', 48.29);
INSERT INTO #f1
VALUES
('EBAY', 1999, 'Net Income', 9.57);
INSERT INTO #f1
VALUES
('AMZN', 2008, 'Net Income', 645);
INSERT INTO #f1
VALUES
('AMZN', 2007, 'Net Income', 476);
INSERT INTO #f1
VALUES
('AMZN', 2006, 'Net Income', 190);
INSERT INTO #f1
VALUES
('AMZN', 2005, 'Net Income', 333);
INSERT INTO #f1
VALUES
('AMZN', 2004, 'Net Income', 588.45);
INSERT INTO #f1
VALUES
('AMZN', 2003, 'Net Income', 35.28);
INSERT INTO #f1
VALUES
('AMZN', 2002, 'Net Income', -149.93);
INSERT INTO #f1
VALUES
('AMZN', 2001, 'Net Income', -556.75);
INSERT INTO #f1
VALUES
('AMZN', 2000, 'Net Income', -1411.27);
INSERT INTO #f1
VALUES
('AMZN', 1999, 'Net Income', -719.97);
INSERT INTO #f1
VALUES
('CSCO', 2008, 'Net Income', 8052);
INSERT INTO #f1
VALUES
('CSCO', 2007, 'Net Income', 7333);
INSERT INTO #f1
VALUES
('CSCO', 2006, 'Net Income', 5580);
INSERT INTO #f1
VALUES
('CSCO', 2005, 'Net Income', 5741);
INSERT INTO #f1
VALUES
('CSCO', 2004, 'Net Income', 4968);
INSERT INTO #f1
VALUES
('CSCO', 2003, 'Net Income', 3578);
INSERT INTO #f1
VALUES
('CSCO', 2002, 'Net Income', 1893);
INSERT INTO #f1
VALUES
('CSCO', 2001, 'Net Income', -1014);
INSERT INTO #f1
VALUES
('CSCO', 2000, 'Net Income', 2668);
INSERT INTO #f1
VALUES
('CSCO', 1999, 'Net Income', 2023);
INSERT INTO #f1
VALUES
('MSFT', 2008, 'Net Income', 17681);
INSERT INTO #f1
VALUES
('MSFT', 2007, 'Net Income', 14065);
INSERT INTO #f1
VALUES
('MSFT', 2006, 'Net Income', 12599);
INSERT INTO #f1
VALUES
('MSFT', 2005, 'Net Income', 12254);
INSERT INTO #f1
VALUES
('MSFT', 2004, 'Net Income', 8168);
INSERT INTO #f1
VALUES
('MSFT', 2003, 'Net Income', 7531);
INSERT INTO #f1
VALUES
('MSFT', 2002, 'Net Income', 5355);
INSERT INTO #f1
VALUES
('MSFT', 2001, 'Net Income', 7721);
INSERT INTO #f1
VALUES
('MSFT', 2000, 'Net Income', 9421);
INSERT INTO #f1
VALUES
('MSFT', 1999, 'Net Income', 7785);
INSERT INTO #f1
VALUES
('GOOG', 2008, 'EPS', 13.31);
INSERT INTO #f1
VALUES
('GOOG', 2007, 'EPS', 13.29);
INSERT INTO #f1
VALUES
('GOOG', 2006, 'EPS', 9.94);
INSERT INTO #f1
VALUES
('GOOG', 2005, 'EPS', 5.02);
INSERT INTO #f1
VALUES
('GOOG', 2004, 'EPS', 1.46);
INSERT INTO #f1
VALUES
('GOOG', 2003, 'EPS', 0.41);
INSERT INTO #f1
VALUES
('GOOG', 2002, 'EPS', 0.45);
INSERT INTO #f1
VALUES
('GOOG', 2001, 'EPS', 0.04);
INSERT INTO #f1
VALUES
('GOOG', 2000, 'EPS', -0.22);
INSERT INTO #f1
VALUES
('GOOG', 1999, 'EPS', -0.14);
INSERT INTO #f1
VALUES
('EBAY', 2008, 'EPS', 1.36);
INSERT INTO #f1
VALUES
('EBAY', 2007, 'EPS', 0.25);
INSERT INTO #f1
VALUES
('EBAY', 2006, 'EPS', 0.79);
INSERT INTO #f1
VALUES
('EBAY', 2005, 'EPS', 0.78);
INSERT INTO #f1
VALUES
('EBAY', 2004, 'EPS', 0.57);
INSERT INTO #f1
VALUES
('EBAY', 2003, 'EPS', 0.34);
INSERT INTO #f1
VALUES
('EBAY', 2002, 'EPS', 0.21);
INSERT INTO #f1
VALUES
('EBAY', 2001, 'EPS', 0.08);
INSERT INTO #f1
VALUES
('EBAY', 2000, 'EPS', 0.04);
INSERT INTO #f1
VALUES
('EBAY', 1999, 'EPS', 0.01);
INSERT INTO #f1
VALUES
('AMZN', 2008, 'EPS', 1.49);
INSERT INTO #f1
VALUES
('AMZN', 2007, 'EPS', 1.12);
INSERT INTO #f1
VALUES
('AMZN', 2006, 'EPS', 0.45);
INSERT INTO #f1
VALUES
('AMZN', 2005, 'EPS', 0.78);
INSERT INTO #f1
VALUES
('AMZN', 2004, 'EPS', 1.39);
INSERT INTO #f1
VALUES
('AMZN', 2003, 'EPS', 0.08);
INSERT INTO #f1
VALUES
('AMZN', 2002, 'EPS', -0.4);
INSERT INTO #f1
VALUES
('AMZN', 2001, 'EPS', -1.53);
INSERT INTO #f1
VALUES
('AMZN', 2000, 'EPS', -4.02);
INSERT INTO #f1
VALUES
('AMZN', 1999, 'EPS', -2.2);
INSERT INTO #f1
VALUES
('CSCO', 2008, 'EPS', 1.31);
INSERT INTO #f1
VALUES
('CSCO', 2007, 'EPS', 1.17);
INSERT INTO #f1
VALUES
('CSCO', 2006, 'EPS', 0.89);
INSERT INTO #f1
VALUES
('CSCO', 2005, 'EPS', 0.87);
INSERT INTO #f1
VALUES
('CSCO', 2004, 'EPS', 0.7);
INSERT INTO #f1
VALUES
('CSCO', 2003, 'EPS', 0.5);
INSERT INTO #f1
VALUES
('CSCO', 2002, 'EPS', 0.25);
INSERT INTO #f1
VALUES
('CSCO', 2001, 'EPS', -0.14);
INSERT INTO #f1
VALUES
('CSCO', 2000, 'EPS', 0.36);
INSERT INTO #f1
VALUES
('CSCO', 1999, 'EPS', 0.29);
INSERT INTO #f1
VALUES
('MSFT', 2008, 'EPS', 1.87);
INSERT INTO #f1
VALUES
('MSFT', 2007, 'EPS', 1.42);
INSERT INTO #f1
VALUES
('MSFT', 2006, 'EPS', 1.2);
INSERT INTO #f1
VALUES
('MSFT', 2005, 'EPS', 1.12);
INSERT INTO #f1
VALUES
('MSFT', 2004, 'EPS', 0.75);
INSERT INTO #f1
VALUES
('MSFT', 2003, 'EPS', 0.69);
INSERT INTO #f1
VALUES
('MSFT', 2002, 'EPS', 0.48);
INSERT INTO #f1
VALUES
('MSFT', 2001, 'EPS', 0.69);
INSERT INTO #f1
VALUES
('MSFT', 2000, 'EPS', 0.85);
INSERT INTO #f1
VALUES
('MSFT', 1999, 'EPS', 0.71);
INSERT INTO #f1
VALUES
('GOOG', 2008, 'Tax Rate', 27.79);
INSERT INTO #f1
VALUES
('GOOG', 2007, 'Tax Rate', 25.91);
INSERT INTO #f1
VALUES
('GOOG', 2006, 'Tax Rate', 23.28);
INSERT INTO #f1
VALUES
('GOOG', 2005, 'Tax Rate', 31.58);
INSERT INTO #f1
VALUES
('GOOG', 2004, 'Tax Rate', 38.62);
INSERT INTO #f1
VALUES
('GOOG', 2003, 'Tax Rate', 69.52);
INSERT INTO #f1
VALUES
('GOOG', 2002, 'Tax Rate', 46.11);
INSERT INTO #f1
VALUES
('GOOG', 2001, 'Tax Rate', 30.62);
INSERT INTO #f1
VALUES
('GOOG', 2000, 'Tax Rate', 0);
INSERT INTO #f1
VALUES
('GOOG', 1999, 'Tax Rate', 0);
INSERT INTO #f1
VALUES
('EBAY', 2008, 'Tax Rate', 18.51);
INSERT INTO #f1
VALUES
('EBAY', 2007, 'Tax Rate', 53.62);
INSERT INTO #f1
VALUES
('EBAY', 2006, 'Tax Rate', 27.24);
INSERT INTO #f1
VALUES
('EBAY', 2005, 'Tax Rate', 30.16);
INSERT INTO #f1
VALUES
('EBAY', 2004, 'Tax Rate', 30.48);
INSERT INTO #f1
VALUES
('EBAY', 2003, 'Tax Rate', 31.25);
INSERT INTO #f1
VALUES
('EBAY', 2002, 'Tax Rate', 36.66);
INSERT INTO #f1
VALUES
('EBAY', 2001, 'Tax Rate', 49.1);
INSERT INTO #f1
VALUES
('EBAY', 2000, 'Tax Rate', 41.98);
INSERT INTO #f1
VALUES
('EBAY', 1999, 'Tax Rate', 46.7);
INSERT INTO #f1
VALUES
('AMZN', 2008, 'Tax Rate', 27.41);
INSERT INTO #f1
VALUES
('AMZN', 2007, 'Tax Rate', 27.88);
INSERT INTO #f1
VALUES
('AMZN', 2006, 'Tax Rate', 49.6);
INSERT INTO #f1
VALUES
('AMZN', 2005, 'Tax Rate', 22.2);
INSERT INTO #f1
VALUES
('AMZN', 2004, 'Tax Rate', -65.36);
INSERT INTO #f1
VALUES
('AMZN', 2003, 'Tax Rate', 9.51);
INSERT INTO #f1
VALUES
('AMZN', 2002, 'Tax Rate', 0);
INSERT INTO #f1
VALUES
('AMZN', 2001, 'Tax Rate', 0);
INSERT INTO #f1
VALUES
('AMZN', 2000, 'Tax Rate', 0);
INSERT INTO #f1
VALUES
('AMZN', 1999, 'Tax Rate', 0);
INSERT INTO #f1
VALUES
('CSCO', 2008, 'Tax Rate', 21.48);
INSERT INTO #f1
VALUES
('CSCO', 2007, 'Tax Rate', 22.49);
INSERT INTO #f1
VALUES
('CSCO', 2006, 'Tax Rate', 26.9);
INSERT INTO #f1
VALUES
('CSCO', 2005, 'Tax Rate', 28.56);
INSERT INTO #f1
VALUES
('CSCO', 2004, 'Tax Rate', 28.95);
INSERT INTO #f1
VALUES
('CSCO', 2003, 'Tax Rate', 28.63);
INSERT INTO #f1
VALUES
('CSCO', 2002, 'Tax Rate', 30.15);
INSERT INTO #f1
VALUES
('CSCO', 2001, 'Tax Rate', 0);
INSERT INTO #f1
VALUES
('CSCO', 2000, 'Tax Rate', 38.57);
INSERT INTO #f1
VALUES
('CSCO', 1999, 'Tax Rate', 36.84);
INSERT INTO #f1
VALUES
('MSFT', 2008, 'Tax Rate', 25.75);
INSERT INTO #f1
VALUES
('MSFT', 2007, 'Tax Rate', 30.03);
INSERT INTO #f1
VALUES
('MSFT', 2006, 'Tax Rate', 31.01);
INSERT INTO #f1
VALUES
('MSFT', 2005, 'Tax Rate', 26.31);
INSERT INTO #f1
VALUES
('MSFT', 2004, 'Tax Rate', 33.03);
INSERT INTO #f1
VALUES
('MSFT', 2003, 'Tax Rate', 31.87);
INSERT INTO #f1
VALUES
('MSFT', 2002, 'Tax Rate', 32);
INSERT INTO #f1
VALUES
('MSFT', 2001, 'Tax Rate', 33.01);
INSERT INTO #f1
VALUES
('MSFT', 2000, 'Tax Rate', 34);
INSERT INTO #f1
VALUES
('MSFT', 1999, 'Tax Rate', 34.53);
select 'GOOG' as [Ticker],
wct.TREND_q(
'SELECT Amount
,FY from #f1 where symbol = ' + char(39) + 'GOOG' + Char(39) + ' and DESCR = ' +
char(39) + 'Sales' + Char(39)
+ ' and FY > 2003',
2009
) as [2009 Sales];
This produces the following result.
{"columns":[{"field":"Ticker"},{"field":"2009 Sales","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"Ticker":"GOOG","2009 Sales":"25964.8750000019"}]}
Using the normalized table #f1 , forecast the sales for all the companies in 2009, based on data subsequent to the 2008 fiscal year.
select distinct
f.symbol as [Ticker],
wct.TREND_q(
'SELECT Amount
,FY from #f1 where symbol = ' + char(39) + f.symbol + Char(39) + ' and DESCR = '
+ char(39) + 'Sales' + Char(39)
+ ' and FY > 2003',
2009
) as [2009 Sales]
from #f1 f;
This produces the following result.
{"columns":[{"field":"Ticker"},{"field":"2009 Sales","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"Ticker":"AMZN","2009 Sales":"21275.0519999992"},{"Ticker":"CSCO","2009 Sales":"43491.6999999993"},{"Ticker":"EBAY","2009 Sales":"10099.3569999998"},{"Ticker":"GOOG","2009 Sales":"25964.8750000019"},{"Ticker":"MSFT","2009 Sales":"64040.6000000015"}]}
See Also
SLOPE - slope of the linear regression through the data points in the known x-values and y-values
STEYX - the standard error of the predicted y-value for each x in the regression
CORREL - Aggregate function to calculate the correlation coefficient
FORECAST - The predicted y-value for a given x-value
TRENDMX - Calculate the values along a linear trend for multiple x values
TRENDMX_Q - Calculate the values along a linear trend for multiple x values
LINEST - the Ordinary Least Squares (OLS) solution for a series of x-values and y-values
LINEST_Q - the Ordinary Least Squares (OLS) solution for a series of x-values and y-values