SQL Server moving FORECAST function

MovingFORECAST

Updated: 31 Oct 2012

Use the MovingFORECAST 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 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 [Example].[wct].[MovingFORECAST](

<@New_x,float,>

,<@Y,float,>

,<@X,float,>

,<@Offset,int,>

,<@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.

@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.

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.

Example

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.