SQL Server MovingFORECAST Function

Updated 2023-11-13 21:15:15.150000

Description

Use the scalar function MovingFORECAST 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 intercept value is calculated for each value from the first value in the window to the last value in the window. If the column values are presented to the functions out of order, an error message will be generated.

Syntax

SELECT [westclintech].[wct].[MovingFORECAST](
  <@New_x, float,>
 ,<@Y, float,>
 ,<@X, float,>
 ,<@Offset, int,>
 ,<@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.

@Offset

specifies the window size. @Offset is an expression of type int or of a type that can be implicitly converted to int.

@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 MovingFORECAST calculation. @Id allows you to specify multiple MovingFORECAST 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 forecast from the first row of a dataset or of a partition of x- and y-values use the RunningFORECAST function.

If @RowNum = 1 then MovingFORECAST 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.

Set @X to NULL to have the function maintain a constant set of x-values in the range 1 to window-size.

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 MovingFORECAST function to predict the new month's sales based on the preceding 6 months 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.MovingFORECAST(
                                  RANK() OVER (ORDER BY EOM) + 1,
                                  sales,
                                  RANK() OVER (ORDER BY EOM),
                                  6,
                                  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.

In this example, we will set @x to NULL and @new_x to 7 to generate a forecast for the 7 month using the preceding 6 months.

SELECT cast(EOM as date) as EOM
,Item
,SALES
,CAST(wct.MovingFORECAST(7, sales, NULL,6,RANK() OVER (ORDER BY EOM),NULL) as money) as FORECAST
FROM #f
WHERE item = 'Leaf Blowers';

This produces the following result.

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]
,ROUND(wct.MovingFORECAST(7, f1.SALES, NULL, 6, ROW_NUMBER() OVER (ORDER BY f1.EOM),1), 0) as [FORECAST]
,f2.SALES as [SB]
,ROUND(wct.MovingFORECAST(7, f2.SALES, NULL, 6, ROW_NUMBER() OVER (ORDER BY f2.EOM),2), 0) as [FORECAST]
,f3.SALES as [PS]
,ROUND(wct.MovingFORECAST(7, f3.SALES, NULL, 6, ROW_NUMBER() OVER (ORDER BY f2.EOM),3), 0) as [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.