FORECAST_q
Updated: 9 August 2010
Use 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.
Syntax
SELECT [wctStatistics].[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 Types
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, FORECAST_q will return an error
· Use FORECAST_q for complex queries on normalized data. For simple queries or queries on de-normalized data, consider using FORECAST
· 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
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
Ticker 2009 Sales
------ ----------------------
GOOG 25964.8750000019
(1 row(s) affected)
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
Ticker 2009 Sales
---------- ----------------------
AMZN 21275.0519999992
CSCO 43491.6999999993
EBAY 10099.3569999998
GOOG 25964.8750000019
MSFT 64040.6000000015
(5 row(s) affected)
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) + 'Sales' + Char(39)
+ ' and FY > 2003') as [2009 Sales]
from myCTE
This produces the following result
Ticker Year EPS
------ ----------- ----------------------
GOOG 2009 18.1950000000006
GOOG 2010 21.3920000000007
GOOG 2011 24.5890000000009
GOOG 2012 27.786000000001
GOOG 2013 30.9830000000002
GOOG 2014 34.1800000000003
(6 row(s) affected)
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.
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(mycte.FY,'#f2','EPS','FY','SYMBOL', f.SYMBOL) as [EPS]
from mycte, #f2 f
This produces the following result
Ticker Year EPS
---------- ----------- ----------------------
AMZN 2009 1.20800000000001
AMZN 2010 1.262
AMZN 2011 1.316
AMZN 2012 1.37
AMZN 2013 1.42400000000001
AMZN 2014 1.47800000000001
CSCO 2009 1.44400000000002
CSCO 2010 1.596
CSCO 2011 1.74799999999999
CSCO 2012 1.90000000000003
CSCO 2013 2.05200000000002
CSCO 2014 2.20400000000001
EBAY 2009 1.065
EBAY 2010 1.16999999999999
EBAY 2011 1.27500000000001
EBAY 2012 1.38
EBAY 2013 1.48499999999999
EBAY 2014 1.59
GOOG 2009 18.1950000000006
GOOG 2010 21.3920000000007
GOOG 2011 24.5890000000009
GOOG 2012 27.786000000001
GOOG 2013 30.9830000000002
GOOG 2014 34.1800000000003
MSFT 2009 2.03399999999999
MSFT 2010 2.28800000000001
MSFT 2011 2.54199999999997
MSFT 2012 2.79599999999999
MSFT 2013 3.05000000000001
MSFT 2014 3.30399999999997
(30 row(s) affected)
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 Amount
,FY from #f1 where symbol = ' + char(39) + f.symbol + Char(39)
+ ' and DESCR = ' + char(39) + 'EPS' + Char(39)
+ ' and FY > 2003'), 3) as [EPS]
from myCTE, #f1 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
|
Ticker
|
2009
|
2010
|
2011
|
2012
|
2013
|
2014
|
|
AMZN
|
1.208
|
1.262
|
1.316
|
1.37
|
1.424
|
1.478
|
|
CSCO
|
1.444
|
1.596
|
1.748
|
1.9
|
2.052
|
2.204
|
|
EBAY
|
1.065
|
1.17
|
1.275
|
1.38
|
1.485
|
1.59
|
|
GOOG
|
18.195
|
21.392
|
24.589
|
27.786
|
30.983
|
34.18
|
|
MSFT
|
2.034
|
2.288
|
2.542
|
2.796
|
3.05
|
3.304
|
See Also