SQL Server RunningFORECAST Function
Updated 2023-11-14 14:38:34.677000
Description
Use the scalar function RunningFORECAST 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 [westclintech].[wct].[RunningFORECAST](
<@New_x, float,>
,<@Y, float,>
,<@X, float,>
,<@RowNum, int,>
,<@Id, tinyint,>)
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.
Return Type
float
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.
Examples
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 |