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