SQL Server FORECAST_q Function

Updated 2023-11-01 10:39:36.943000

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}

Syntax

SELECT [westclintech].[wct].[FORECAST_q] (
  <@X, float,>
 ,<@Known_y_Known_x_RangeQuery, nvarchar(max),>)

Arguments

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

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

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.

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.

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.

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.

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.

See Also

SLOPE - slope of the linear regression through the data points in the known x-values and y-values

INTERCEPT - the point at which a line will intersect the y-axis by using existing x-values and y-values

RSQ - the Pearson product moment correlation coefficient through data points in known-y's and known-x's

STEYX - the standard error of the predicted y-value for each x in the regression

CORREL - Aggregate function to calculate the correlation coefficient

TREND - for simpler queries

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