Login     Register

        Contact Us     Search

XLeratorDB/windowing Documentation

SQL Server running FORECAST function


RunningFORECAST

Updated: 31 Oct 2012


Use the RunningFORECAST function to calculate the predicted value of y for a specific value of x for a series of x- and y-values within a resultant table or partition, without the need for a self-join. The forecast value is calculated from the first row of the resultant table or partition through to the current row. If the column values are presented to the functions out of order, an error message will be generated.
Syntax
SELECT [Example].[wct].[RunningFORECAST](
  <@New_x, float,>
 ,<@Y, float,>
 ,<@X, float,>
 ,<@RowNum, int,>
 ,<@Id, tinyint,>)
GO
Arguments
@New_x
the specific x-value used to forecast the y-value. @New_x is an expression of type float or of a type that can be implicitly converted to float.
@Y
the y-value passed into the function. @Y is an expression of type float or of a type that can be implicitly converted to float.
@X
the x-value passed into the function. @X is an expression of type float or of a type that can be implicitly converted to float.
@RowNum
the number of the row within the group for which the sum is being calculated. If @RowNum for the current row in a set is less than or equal to the previous @RowNum and @RowNum is not equal to 1, an error message will be generated. @RowNum is an expression of type int or of a type that can be implicitly converted to int.
@Id
a unique identifier for the RunningFORECAST calculation. @Id allows you to specify multiple RunningFORECAST calculations within a resultant table. @Id is an expression of type tinyint or of a type that can be implicitly converted to tinyint.
Remarks
·         If @Id is NULL then @Id = 0.
·         @RowNum must be in ascending order.
·         To calculate the slope over a window of x- and y-values use the MovingFORECAST function.
·         If @RowNum = 1 then RunningFORECAST is NULL.
·         To calculate a single forecast value for a new x-value and a set of x- and y-values use the FORECAST function.
·         There may be cases where the order in which the data are returned to the function and the order in which the results are returned are different, generally due to parallelism. You can use OPTION(MAXDOP 1) or OPTION(MAXDOP 1,FORCE ORDER) to help eliminate this problem.
Example
In this example we will store the monthly sales for three products: Leaf Blowers, Snow Blowers, and Pool Supplies. We will use the RunningFORECAST function to predict the new month’s sales based on the historical sales. We will use the RANK() function to number the months, with the earliest month being assigned a rank of 1.
--Create the temporary table
CREATE TABLE #f(
      EOM         datetime,
      item varchar(20),
      sales money
      )
--Populate the table with some data
INSERT INTO #f VALUES ('2010-10-31','Leaf Blowers',42548)
INSERT INTO #f VALUES ('2010-11-30','Leaf Blowers',77227)
INSERT INTO #f VALUES ('2010-12-31','Leaf Blowers',66944)
INSERT INTO #f VALUES ('2011-01-31','Leaf Blowers',34591)
INSERT INTO #f VALUES ('2011-02-28','Leaf Blowers',73468)
INSERT INTO #f VALUES ('2011-03-31','Leaf Blowers',50102)
INSERT INTO #f VALUES ('2011-04-30','Leaf Blowers',87270)
INSERT INTO #f VALUES ('2011-05-31','Leaf Blowers',51555)
INSERT INTO #f VALUES ('2011-06-30','Leaf Blowers',75139)
INSERT INTO #f VALUES ('2011-07-31','Leaf Blowers',50682)
INSERT INTO #f VALUES ('2011-08-31','Leaf Blowers',96577)
INSERT INTO #f VALUES ('2011-09-30','Leaf Blowers',77553)
INSERT INTO #f VALUES ('2011-10-31','Leaf Blowers',45299)
INSERT INTO #f VALUES ('2011-11-30','Leaf Blowers',71815)
INSERT INTO #f VALUES ('2011-12-31','Leaf Blowers',45070)
INSERT INTO #f VALUES ('2012-01-31','Leaf Blowers',60712)
INSERT INTO #f VALUES ('2012-02-29','Leaf Blowers',50021)
INSERT INTO #f VALUES ('2012-03-31','Leaf Blowers',38495)
INSERT INTO #f VALUES ('2012-04-30','Leaf Blowers',49125)
INSERT INTO #f VALUES ('2012-05-31','Leaf Blowers',49227)
INSERT INTO #f VALUES ('2012-06-30','Leaf Blowers',61511)
INSERT INTO #f VALUES ('2012-07-31','Leaf Blowers',66185)
INSERT INTO #f VALUES ('2012-08-31','Leaf Blowers',59871)
INSERT INTO #f VALUES ('2012-09-30','Leaf Blowers',69951)
INSERT INTO #f VALUES ('2012-10-31','Leaf Blowers',84861)
INSERT INTO #f VALUES ('2012-11-30','Leaf Blowers',79946)
INSERT INTO #f VALUES ('2010-10-31','Snow Blowers',77554)
INSERT INTO #f VALUES ('2010-11-30','Snow Blowers',89677)
INSERT INTO #f VALUES ('2010-12-31','Snow Blowers',75063)
INSERT INTO #f VALUES ('2011-01-31','Snow Blowers',57609)
INSERT INTO #f VALUES ('2011-02-28','Snow Blowers',65206)
INSERT INTO #f VALUES ('2011-03-31','Snow Blowers',50178)
INSERT INTO #f VALUES ('2011-04-30','Snow Blowers',41676)
INSERT INTO #f VALUES ('2011-05-31','Snow Blowers',50024)
INSERT INTO #f VALUES ('2011-06-30','Snow Blowers',35835)
INSERT INTO #f VALUES ('2011-07-31','Snow Blowers',71655)
INSERT INTO #f VALUES ('2011-08-31','Snow Blowers',69309)
INSERT INTO #f VALUES ('2011-09-30','Snow Blowers',50066)
INSERT INTO #f VALUES ('2011-10-31','Snow Blowers',77390)
INSERT INTO #f VALUES ('2011-11-30','Snow Blowers',58315)
INSERT INTO #f VALUES ('2011-12-31','Snow Blowers',83867)
INSERT INTO #f VALUES ('2012-01-31','Snow Blowers',92994)
INSERT INTO #f VALUES ('2012-02-29','Snow Blowers',67718)
INSERT INTO #f VALUES ('2012-03-31','Snow Blowers',79875)
INSERT INTO #f VALUES ('2012-04-30','Snow Blowers',30774)
INSERT INTO #f VALUES ('2012-05-31','Snow Blowers',33199)
INSERT INTO #f VALUES ('2012-06-30','Snow Blowers',33284)
INSERT INTO #f VALUES ('2012-07-31','Snow Blowers',30369)
INSERT INTO #f VALUES ('2012-08-31','Snow Blowers',50885)
INSERT INTO #f VALUES ('2012-09-30','Snow Blowers',81832)
INSERT INTO #f VALUES ('2012-10-31','Snow Blowers',72875)
INSERT INTO #f VALUES ('2012-11-30','Snow Blowers',56955)
INSERT INTO #f VALUES ('2010-10-31','Pool Supplies',67437)
INSERT INTO #f VALUES ('2010-11-30','Pool Supplies',67760)
INSERT INTO #f VALUES ('2010-12-31','Pool Supplies',36603)
INSERT INTO #f VALUES ('2011-01-31','Pool Supplies',67072)
INSERT INTO #f VALUES ('2011-02-28','Pool Supplies',71843)
INSERT INTO #f VALUES ('2011-03-31','Pool Supplies',67283)
INSERT INTO #f VALUES ('2011-04-30','Pool Supplies',62408)
INSERT INTO #f VALUES ('2011-05-31','Pool Supplies',57671)
INSERT INTO #f VALUES ('2011-06-30','Pool Supplies',95730)
INSERT INTO #f VALUES ('2011-07-31','Pool Supplies',58017)
INSERT INTO #f VALUES ('2011-08-31','Pool Supplies',88317)
INSERT INTO #f VALUES ('2011-09-30','Pool Supplies',63141)
INSERT INTO #f VALUES ('2011-10-31','Pool Supplies',43968)
INSERT INTO #f VALUES ('2011-11-30','Pool Supplies',60566)
INSERT INTO #f VALUES ('2011-12-31','Pool Supplies',33517)
INSERT INTO #f VALUES ('2012-01-31','Pool Supplies',37272)
INSERT INTO #f VALUES ('2012-02-29','Pool Supplies',76982)
INSERT INTO #f VALUES ('2012-03-31','Pool Supplies',43459)
INSERT INTO #f VALUES ('2012-04-30','Pool Supplies',66698)
INSERT INTO #f VALUES ('2012-05-31','Pool Supplies',76722)
INSERT INTO #f VALUES ('2012-06-30','Pool Supplies',88796)
INSERT INTO #f VALUES ('2012-07-31','Pool Supplies',53017)
INSERT INTO #f VALUES ('2012-08-31','Pool Supplies',93040)
INSERT INTO #f VALUES ('2012-09-30','Pool Supplies',78513)
INSERT INTO #f VALUES ('2012-10-31','Pool Supplies',45990)
INSERT INTO #f VALUES ('2012-11-30','Pool Supplies',72321)
--Calculate the monthly FORECAST for Leaf Blowers
SELECT cast(EOM as date) as EOM
,Item
,SALES
,CAST(wct.RunningFORECAST(RANK() OVER (ORDER BY EOM) + 1, sales, RANK() OVER (ORDER BY EOM),RANK() OVER (ORDER BY EOM),NULL) as money) as FORECAST
FROM #f
WHERE item = 'Leaf Blowers'
--Clean up
DROP TABLE #f
This produces the following result.
EOM        Item                                 SALES              FORECAST
---------- -------------------- --------------------- ---------------------
2010-10-31 Leaf Blowers                      42548.00                  NULL
2010-11-30 Leaf Blowers                      77227.00             111906.00
2010-12-31 Leaf Blowers                      66944.00            86635.6667
2011-01-31 Leaf Blowers                      34591.00              46789.00
2011-02-28 Leaf Blowers                      73468.00              64716.80
2011-03-31 Leaf Blowers                      50102.00              56894.00
2011-04-30 Leaf Blowers                      87270.00            74084.2857
2011-05-31 Leaf Blowers                      51555.00            65906.7857
2011-06-30 Leaf Blowers                      75139.00            71219.6944
2011-07-31 Leaf Blowers                      50682.00              64829.80
2011-08-31 Leaf Blowers                      96577.00            77079.1818
2011-09-30 Leaf Blowers                      77553.00            79385.1212
2011-10-31 Leaf Blowers                      45299.00            71063.3077
2011-11-30 Leaf Blowers                      71815.00            72320.5824
2011-12-31 Leaf Blowers                      45070.00            66117.7429
2012-01-31 Leaf Blowers                      60712.00             65149.025
2012-02-29 Leaf Blowers                      50021.00            61852.9632
2012-03-31 Leaf Blowers                      38495.00            56629.1503
2012-04-30 Leaf Blowers                      49125.00             54606.386
2012-05-31 Leaf Blowers                      49227.00            52969.0789
2012-06-30 Leaf Blowers                      61511.00            53957.8333
2012-07-31 Leaf Blowers                      66185.00            55653.6104
2012-08-31 Leaf Blowers                      59871.00            56004.7115
2012-09-30 Leaf Blowers                      69951.00            57992.5761
2012-10-31 Leaf Blowers                      84861.00              62094.47
2012-11-30 Leaf Blowers                      79946.00            64891.8215
 
 
In this example we will look at the monthly sales and the sales forecast side-by-side for each of the three products.
SELECT cast(f1.EOM as date) as EOM
,f1.SALES as [LB]
,f2.SALES as [SB]
,f3.SALES as [PS]
,ROUND(wct.RunningFORECAST(RANK() OVER (ORDER BY f1.EOM ASC) + 1, f1.SALES, ROW_NUMBER() OVER (ORDER BY f1.EOM),ROW_NUMBER() OVER (ORDER BY f1.EOM),1), 0) as [LB Forecast]
,ROUND(wct.RunningFORECAST(RANK() OVER (ORDER BY f2.EOM ASC) + 1, f2.SALES, ROW_NUMBER() OVER (ORDER BY f2.EOM),ROW_NUMBER() OVER (ORDER BY f2.EOM),2), 0) as [SB Forecast]
,ROUND(wct.RunningFORECAST(RANK() OVER (ORDER BY f3.EOM ASC) + 1, f3.SALES, ROW_NUMBER() OVER (ORDER BY f3.EOM),ROW_NUMBER() OVER (ORDER BY f2.EOM),3), 0) as [PS Forecast]
FROM #f f1
JOIN #f f2
ON f2.EOM = f1.EOM
AND f2.item = 'Snow Blowers'
JOIN #f f3
ON f3.EOM = f1.EOM
AND f3.Item = 'Pool Supplies'
WHERE f1.item = 'Leaf Blowers'
 
This produces the following result.
EOM        LB      SB      PS      LB Forecast SB Forecast PS Forecast
---------- ------- ------- ------- ----------- ----------- -----------
2010-10-31   42548   77554   67437        NULL        NULL        NULL
2010-11-30   77227   89677   67760      111906      101800       68083
2010-12-31   66944   75063   36603       86636       78274       26433
2011-01-31   34591   57609   67072       46789       56364       51655
2011-02-28   73468   65206   71843       64717       55993       64580
2011-03-31   50102   50178   67283       56894       46440       67194
2011-04-30   87270   41676   62408       74084       37211       65658
2011-05-31   51555   50024   57671       65907       36600       62350
2011-06-30   75139   35835   95730       71220       30310       77206
2011-07-31   50682   71655   58017       64830       40847       71776
2011-08-31   96577   69309   88317       77079       47452       78989
2011-09-30   77553   50066   63141       79385       45871       75657
2011-10-31   45299   77390   43968       71063       53218       67248
2011-11-30   71815   58315   60566       72321       53362       65635
2011-12-31   45070   83867   33517       66118       60330       57176
2012-01-31   60712   92994   37272       65149       68092       51502
2012-02-29   50021   67718   76982       61853       68321       56361
2012-03-31   38495   79875   43459       56629       71198       52857
2012-04-30   49125   30774   66698       54606       63199       54908
2012-05-31   49227   33199   76722       52969       57073       58626
2012-06-30   61511   33284   88796       53958       51987       64039
2012-07-31   66185   30369   53017       55654       47193       62094
2012-08-31   59871   50885   93040       56005       46715       67404
2012-09-30   69951   81832   78513       57993       51487       69520
2012-10-31   84861   72875   45990       62094       54181       66131
2012-11-30   79946   56955   72321       64892       54076       67241
 


Copyright 2008-2013 WestClinTech LLC         Privacy Policy        Terms of Service