FORECAST_q
Updated 2023-11-01 10:39:36.943000
Syntax
SELECT [westclintech].[wct].[FORECAST_q] (
<@X, float,>
,<@Known_y_Known_x_RangeQuery, nvarchar(max),>)
Description
Use the scalar function FORECAST_q to calculate a future value by using existing values. The predicted value is a y-value for a given x-value. The known values are existing x-values and y-values, and the new value is predicted using linear regression.
\\ \text{FORECAST} = a+bx \\ \text{where} \\ a = \bar{y}-b\bar{x} \\ \text{and} \\ b = \frac{\sum(x-\bar{x})(y-\bar{y})}{\sum(x-\bar{x})^2}
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.
@X
is the data point for which you want to predict a value. @X is an expression of type float or of a type that can be implicitly converted to float.
Return Type
float
Remarks
If the dataset for the known-x and the dataset for the known-y have a different number of rows, FORECAST_q will return an error.
If the variance for the known-x dataset is equal to zero, FORECAST_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 , forecast the sales for GOOG in 2009, based on data subsequent to the 2003 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.FORECAST_q(
2009,
'SELECT Amount
,FY from #f1 where symbol = ' + char(39) + 'GOOG' + Char(39) + ' and DESCR = ' +
char(39) + 'Sales' + Char(39)
+ ' and FY > 2003'
) 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 2003 fiscal year.
select distinct
f.symbol as [Ticker],
wct.FORECAST_q(
2009,
'SELECT Amount
,FY from #f1 where symbol = ' + char(39) + f.symbol + Char(39) + ' and DESCR = '
+ char(39) + 'Sales' + Char(39)
+ ' and FY > 2003'
) 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"}]}
Using the normalized table #f1, calculate the EPS for GOOG for the next 6 years, based on data subsequent to the 2003 fiscal year.
with mycte
as (Select 2009 as FY
UNION ALL
Select FY + 1
from mycte
where FY < 2014)
select 'GOOG' as [Ticker],
mycte.FY as [Year],
wct.FORECAST_q(
mycte.FY,
'SELECT Amount
,FY from #f1 where symbol = ' + char(39) + 'GOOG' + Char(39) + ' and DESCR = ' +
char(39) + 'EPS' + Char(39)
+ ' and FY > 2003'
) as [2009 Sales]
from myCTE;
This produces the following result.
{"columns":[{"field":"Ticker"},{"field":"Year","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"2009 Sales","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"Ticker":"GOOG","Year":"2009","2009 Sales":"18.1950000000006"},{"Ticker":"GOOG","Year":"2010","2009 Sales":"21.3920000000007"},{"Ticker":"GOOG","Year":"2011","2009 Sales":"24.5890000000009"},{"Ticker":"GOOG","Year":"2012","2009 Sales":"27.786000000001"},{"Ticker":"GOOG","Year":"2013","2009 Sales":"30.9830000000002"},{"Ticker":"GOOG","Year":"2014","2009 Sales":"34.1800000000003"}]}
Using the de-normalized table #f2 , calculate the EPS for all companies for the next 6 years, based on data subsequent to the 2003 fiscal year.
CREATE TABLE #f2
(
[SYMBOL] [nchar](10) NOT NULL,
[FY] [float] NOT NULL,
[SALES] [float] NOT NULL,
[EBIT] [float] NOT NULL,
[DEPREC] [float] NOT NULL,
[NETINC] [float] NOT NULL,
[EPS] [float] NOT NULL,
[TAXRATE] [float] NOT NULL
);
INSERT INTO #f2
VALUES
('GOOG', 2008, 21795.55, 5853.6, 1499.89, 4226.86, 13.31, 27.79);
INSERT INTO #f2
VALUES
('GOOG', 2007, 16593.99, 5673.98, 967.66, 4203.72, 13.29, 25.91);
INSERT INTO #f2
VALUES
('GOOG', 2006, 10604.92, 4011.04, 571.94, 3077.45, 9.94, 23.28);
INSERT INTO #f2
VALUES
('GOOG', 2005, 6138.56, 2141.68, 293.81, 1465.4, 5.02, 31.58);
INSERT INTO #f2
VALUES
('GOOG', 2004, 3189.22, 650.23, 148.47, 399.12, 1.46, 38.62);
INSERT INTO #f2
VALUES
('GOOG', 2003, 1465.93, 346.65, 50.19, 105.65, 0.41, 69.52);
INSERT INTO #f2
VALUES
('GOOG', 2002, 439.51, 184.92, 18.03, 99.66, 0.45, 46.11);
INSERT INTO #f2
VALUES
('GOOG', 2001, 86.43, 10.07, 10.03, 6.99, 0.04, 30.62);
INSERT INTO #f2
VALUES
('GOOG', 2000, 19.11, -14.69, 0, -14.69, -0.22, 0);
INSERT INTO #f2
VALUES
('GOOG', 1999, 0.22, -6.08, 0, -6.08, -0.14, 0);
INSERT INTO #f2
VALUES
('EBAY', 2008, 8541.26, 2183.56, 719.81, 1779.47, 1.36, 18.51);
INSERT INTO #f2
VALUES
('EBAY', 2007, 7672.33, 750.85, 576.61, 348.25, 0.25, 53.62);
INSERT INTO #f2
VALUES
('EBAY', 2006, 5969.74, 1547.06, 521.63, 1125.64, 0.79, 27.24);
INSERT INTO #f2
VALUES
('EBAY', 2005, 4552.4, 1549.33, 369.54, 1082.04, 0.78, 30.16);
INSERT INTO #f2
VALUES
('EBAY', 2004, 3271.31, 1128.23, 249.42, 778.22, 0.57, 30.48);
INSERT INTO #f2
VALUES
('EBAY', 2003, 2165.1, 661.5, 156.46, 447.18, 0.34, 31.25);
INSERT INTO #f2
VALUES
('EBAY', 2002, 1214.1, 398.13, 76.64, 249.89, 0.21, 36.66);
INSERT INTO #f2
VALUES
('EBAY', 2001, 748.82, 162.94, 78.39, 90.45, 0.08, 49.1);
INSERT INTO #f2
VALUES
('EBAY', 2000, 431.42, 77.96, 38.13, 48.29, 0.04, 41.98);
INSERT INTO #f2
VALUES
('EBAY', 1999, 224.72, 18.14, 19.75, 9.57, 0.01, 46.7);
INSERT INTO #f2
VALUES
('AMZN', 2008, 19166, 901, 340, 645, 1.49, 27.41);
INSERT INTO #f2
VALUES
('AMZN', 2007, 14835, 660, 271, 476, 1.12, 27.88);
INSERT INTO #f2
VALUES
('AMZN', 2006, 10711, 377, 210, 190, 0.45, 49.6);
INSERT INTO #f2
VALUES
('AMZN', 2005, 8490, 428, 118, 333, 0.78, 22.2);
INSERT INTO #f2
VALUES
('AMZN', 2004, 6921.12, 355.87, 75.66, 588.45, 1.39, -65.36);
INSERT INTO #f2
VALUES
('AMZN', 2003, 5263.7, 38.99, 72.74, 35.28, 0.08, 9.51);
INSERT INTO #f2
VALUES
('AMZN', 2002, 3932.94, -150.63, 81.66, -149.93, -0.4, 0);
INSERT INTO #f2
VALUES
('AMZN', 2001, 3122.43, -526.43, 264.03, -556.75, -1.53, 0);
INSERT INTO #f2
VALUES
('AMZN', 2000, 2761.98, -1106.68, 404.78, -1411.27, -4.02, 0);
INSERT INTO #f2
VALUES
('AMZN', 1999, 1639.84, -643.2, 251.5, -719.97, -2.2, 0);
INSERT INTO #f2
VALUES
('CSCO', 2008, 39540, 10255, 1977, 8052, 1.31, 21.48);
INSERT INTO #f2
VALUES
('CSCO', 2007, 34922, 9461, 1569, 7333, 1.17, 22.49);
INSERT INTO #f2
VALUES
('CSCO', 2006, 28484, 7633, 1353, 5580, 0.89, 26.9);
INSERT INTO #f2
VALUES
('CSCO', 2005, 24801, 8036, 1020, 5741, 0.87, 28.56);
INSERT INTO #f2
VALUES
('CSCO', 2004, 22045, 6992, 1199, 4968, 0.7, 28.95);
INSERT INTO #f2
VALUES
('CSCO', 2003, 18878, 5013, 1463, 3578, 0.5, 28.63);
INSERT INTO #f2
VALUES
('CSCO', 2002, 18915, 2710, 1957, 1893, 0.25, 30.15);
INSERT INTO #f2
VALUES
('CSCO', 2001, 22293, -874, 2236, -1014, -0.14, 0);
INSERT INTO #f2
VALUES
('CSCO', 2000, 18928, 4343, 863, 2668, 0.36, 38.57);
INSERT INTO #f2
VALUES
('CSCO', 1999, 12173, 3203, 489, 2023, 0.29, 36.84);
INSERT INTO #f2
VALUES
('MSFT', 2008, 60420, 23814, 1872, 17681, 1.87, 25.75);
INSERT INTO #f2
VALUES
('MSFT', 2007, 51122, 20101, 1406, 14065, 1.42, 30.03);
INSERT INTO #f2
VALUES
('MSFT', 2006, 44282, 18262, 990, 12599, 1.2, 31.01);
INSERT INTO #f2
VALUES
('MSFT', 2005, 39788, 16628, 884, 12254, 1.12, 26.31);
INSERT INTO #f2
VALUES
('MSFT', 2004, 36835, 12196, 817, 8168, 0.75, 33.03);
INSERT INTO #f2
VALUES
('MSFT', 2003, 32187, 11054, 1090, 7531, 0.69, 31.87);
INSERT INTO #f2
VALUES
('MSFT', 2002, 28365, 7875, 1014, 5355, 0.48, 32);
INSERT INTO #f2
VALUES
('MSFT', 2001, 25296, 11525, 1238, 7721, 0.69, 33.01);
INSERT INTO #f2
VALUES
('MSFT', 2000, 22956, 14275, 900, 9421, 0.85, 34);
INSERT INTO #f2
VALUES
('MSFT', 1999, 19747, 11891, 483, 7785, 0.71, 34.53);
with mycte
as (Select 2009 as FY
UNION ALL
Select FY + 1
from mycte
where FY < 2014)
select distinct
f.symbol as [Ticker],
mycte.FY as [FY],
wct.FORECAST_q(mycte.FY, 'SELECT EPS, FY FROM #f2 WHERE FY > 2003 and symbol
= ' + char(39) + f.SYMBOL + Char(39)) as [EPS]
from mycte,
#f2 f;
This produces the following result.
{"columns":[{"field":"Ticker"},{"field":"FY","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"EPS","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"Ticker":"AMZN","FY":"2009","EPS":"1.20800000000001"},{"Ticker":"AMZN","FY":"2010","EPS":"1.262"},{"Ticker":"AMZN","FY":"2011","EPS":"1.316"},{"Ticker":"AMZN","FY":"2012","EPS":"1.37"},{"Ticker":"AMZN","FY":"2013","EPS":"1.42400000000001"},{"Ticker":"AMZN","FY":"2014","EPS":"1.47800000000001"},{"Ticker":"CSCO","FY":"2009","EPS":"1.44400000000002"},{"Ticker":"CSCO","FY":"2010","EPS":"1.596"},{"Ticker":"CSCO","FY":"2011","EPS":"1.74799999999999"},{"Ticker":"CSCO","FY":"2012","EPS":"1.90000000000003"},{"Ticker":"CSCO","FY":"2013","EPS":"2.05200000000002"},{"Ticker":"CSCO","FY":"2014","EPS":"2.20400000000001"},{"Ticker":"EBAY","FY":"2009","EPS":"1.065"},{"Ticker":"EBAY","FY":"2010","EPS":"1.16999999999999"},{"Ticker":"EBAY","FY":"2011","EPS":"1.27500000000001"},{"Ticker":"EBAY","FY":"2012","EPS":"1.38"},{"Ticker":"EBAY","FY":"2013","EPS":"1.48499999999999"},{"Ticker":"EBAY","FY":"2014","EPS":"1.59"},{"Ticker":"GOOG","FY":"2009","EPS":"18.1950000000006"},{"Ticker":"GOOG","FY":"2010","EPS":"21.3920000000007"},{"Ticker":"GOOG","FY":"2011","EPS":"24.5890000000009"},{"Ticker":"GOOG","FY":"2012","EPS":"27.786000000001"},{"Ticker":"GOOG","FY":"2013","EPS":"30.9830000000002"},{"Ticker":"GOOG","FY":"2014","EPS":"34.1800000000003"},{"Ticker":"MSFT","FY":"2009","EPS":"2.03399999999999"},{"Ticker":"MSFT","FY":"2010","EPS":"2.28800000000001"},{"Ticker":"MSFT","FY":"2011","EPS":"2.54199999999997"},{"Ticker":"MSFT","FY":"2012","EPS":"2.79599999999999"},{"Ticker":"MSFT","FY":"2013","EPS":"3.05000000000001"},{"Ticker":"MSFT","FY":"2014","EPS":"3.30399999999997"}]}
If we wanted to represent the results in tabular format, we could use the PIVOT statement.
with mycte
as (Select 2009 as FY
UNION ALL
Select FY + 1
from mycte
where FY < 2014)
SELECT Ticker,
[2009] as [2009],
[2010] as [2010],
[2011] as [2011],
[2012] as [2012],
[2013] as [2013],
[2014] as [2014]
FROM
(
select distinct
f.symbol as [Ticker],
mycte.FY as [FY],
Round(
wct.FORECAST_q(
mycte.FY,
'SELECT EPS, FY FROM #f2 WHERE FY > 2003 and
symbol = ' + char(39) +
f.SYMBOL
+ Char(39)
),
3
) as [EPS]
from myCTE,
#f2 f
) as src
PIVOT
(
SUM(EPS)
FOR FY in ([2009], [2010], [2011], [2012], [2013], [2014])
) as pvt
order by Ticker;
This produces the following result.
{"columns":[{"field":"Ticker"},{"field":"2009","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"2010","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"2011","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"2012","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"2013","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"2014","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"2009":"1.208","2010":"1.262","2011":"1.316","2012":"1.37","2013":"1.424","2014":"1.478","Ticker":"AMZN"},{"2009":"1.444","2010":"1.596","2011":"1.748","2012":"1.9","2013":"2.052","2014":"2.204","Ticker":"CSCO"},{"2009":"1.065","2010":"1.17","2011":"1.275","2012":"1.38","2013":"1.485","2014":"1.59","Ticker":"EBAY"},{"2009":"18.195","2010":"21.392","2011":"24.589","2012":"27.786","2013":"30.983","2014":"34.18","Ticker":"GOOG"},{"2009":"2.034","2010":"2.288","2011":"2.542","2012":"2.796","2013":"3.05","2014":"3.304","Ticker":"MSFT"}]}
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
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