Login     Register

        Contact Us     Search

XLeratorDB/statistics Documentation

SQL Server FORECAST function


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.

 FORECAST
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


Copyright 2008-2013 WestClinTech LLC         Privacy Policy        Terms of Service