Login     Register

        Contact Us     Search

XLeratorDB/windowing Documentation

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.
--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.
EOM        Item                                 SALES              FORECAST
---------- -------------------- --------------------- ---------------------
2010-10-31 Leaf Blowers                      42548.00                  0.00
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            60043.4286
2011-06-30 Leaf Blowers                      75139.00            73054.8571
2011-07-31 Leaf Blowers                      50682.00            67982.1429
2011-08-31 Leaf Blowers                      96577.00            77592.7143
2011-09-30 Leaf Blowers                      77553.00            84138.8571
2011-10-31 Leaf Blowers                      45299.00            61656.5714
2011-11-30 Leaf Blowers                      71815.00            70941.5714
2011-12-31 Leaf Blowers                      45070.00            51845.1429
2012-01-31 Leaf Blowers                      60712.00            52720.8571
2012-02-29 Leaf Blowers                      50021.00            39066.8571
2012-03-31 Leaf Blowers                      38495.00            38590.2857
2012-04-30 Leaf Blowers                      49125.00            44332.2857
2012-05-31 Leaf Blowers                      49227.00            40370.5714
2012-06-30 Leaf Blowers                      61511.00            54231.1429
2012-07-31 Leaf Blowers                      66185.00            60772.4286
2012-08-31 Leaf Blowers                      59871.00              67393.00
2012-09-30 Leaf Blowers                      69951.00            75311.8571
2012-10-31 Leaf Blowers                      84861.00            83963.8571
2012-11-30 Leaf Blowers                      79946.00            87739.2857
 
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 prodcues the following result.
EOM        Item                                 SALES              FORECAST
---------- -------------------- --------------------- ---------------------
2010-10-31 Leaf Blowers                      42548.00                  NULL
2010-11-30 Leaf Blowers                      77227.00                  NULL
2010-12-31 Leaf Blowers                      66944.00                  NULL
2011-01-31 Leaf Blowers                      34591.00                  NULL
2011-02-28 Leaf Blowers                      73468.00                  NULL
2011-03-31 Leaf Blowers                      50102.00                  NULL
2011-04-30 Leaf Blowers                      87270.00            70997.1429
2011-05-31 Leaf Blowers                      51555.00            60788.1786
2011-06-30 Leaf Blowers                      75139.00            70472.1786
2011-07-31 Leaf Blowers                      50682.00            66086.8571
2011-08-31 Leaf Blowers                      96577.00            75508.5714
2011-09-30 Leaf Blowers                      77553.00            80564.0714
2011-10-31 Leaf Blowers                      45299.00            63530.8214
2011-11-30 Leaf Blowers                      71815.00            69942.6071
2011-12-31 Leaf Blowers                      45070.00            55388.6786
2012-01-31 Leaf Blowers                      60712.00            55530.2143
2012-02-29 Leaf Blowers                      50021.00            45266.1071
2012-03-31 Leaf Blowers                      38495.00            42834.3214
2012-04-30 Leaf Blowers                      49125.00            46125.5357
2012-05-31 Leaf Blowers                      49227.00            43294.5357
2012-06-30 Leaf Blowers                      61511.00            53321.9643
2012-07-31 Leaf Blowers                      66185.00            58982.0357
2012-08-31 Leaf Blowers                      59871.00            63917.4286
2012-09-30 Leaf Blowers                      69951.00            70568.3571
2012-10-31 Leaf Blowers                      84861.00            78713.2857
2012-11-30 Leaf Blowers                      79946.00            82645.6071
 
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.
       EOM        LB  FORECAST        SB  FORECAST        PS  FORECAST
---------- --------- --------- --------- --------- --------- ---------
2010-10-31  42548.00      NULL  77554.00      NULL  67437.00      NULL
2010-11-30  77227.00      NULL  89677.00      NULL  67760.00      NULL
2010-12-31  66944.00      NULL  75063.00      NULL  36603.00      NULL
2011-01-31  34591.00      NULL  57609.00      NULL  67072.00      NULL
2011-02-28  73468.00      NULL  65206.00      NULL  71843.00      NULL
2011-03-31  50102.00      NULL  50178.00      NULL  67283.00      NULL
2011-04-30  87270.00  70997.00  41676.00  44228.00  62408.00  64972.00
2011-05-31  51555.00  60788.00  50024.00  40651.00  57671.00  63829.00
2011-06-30  75139.00  70472.00  35835.00  36900.00  95730.00  81495.00
2011-07-31  50682.00  66087.00  71655.00  51374.00  58017.00  69753.00
2011-08-31  96577.00  75509.00  69309.00  60136.00  88317.00  78490.00
2011-09-30  77553.00  80564.00  50066.00  60881.00  63141.00  74624.00
2011-10-31  45299.00  63531.00  77390.00  71640.00  43968.00  61487.00
2011-11-30  71815.00  69943.00  58315.00  68199.00  60566.00  57161.00
2011-12-31  45070.00  55389.00  83867.00  77223.00  33517.00  39120.00
2012-01-31  60712.00  55530.00  92994.00  82805.00  37272.00  36284.00
2012-02-29  50021.00  45266.00  67718.00  80761.00  76982.00  47374.00
2012-03-31  38495.00  42834.00  79875.00  84114.00  43459.00  49524.00
2012-04-30  49125.00  46126.00  30774.00  58039.00  66698.00  60078.00
2012-05-31  49227.00  43295.00  33199.00  42965.00  76722.00  69426.00
2012-06-30  61511.00  53322.00  33284.00  27214.00  88796.00  85612.00
2012-07-31  66185.00  58982.00  30369.00  20093.00  53017.00  74434.00
2012-08-31  59871.00  63917.00  50885.00  30836.00  93040.00  80822.00
2012-09-30  69951.00  70568.00  81832.00  53238.00  78513.00  85836.00
2012-10-31  84861.00  78713.00  72875.00  73442.00  45990.00  66008.00
2012-11-30  79946.00  82646.00  56955.00  72976.00  72321.00  64773.00
 

 



Copyright 2008-2013 WestClinTech LLC         Privacy Policy        Terms of Service