Calculate forecast average using recursive CTE

Question

I was trying to answer a question here, where I need to calculate a forecast of sales based on the 3 previous months which either can be actuals or forecast.

Month   Actuals Forecast  
1       10    
2       15    
3       17    
4                14.00 
5                15.33  
6                15.44 
7                14.93

Month 4 = (10+15+17)/3 
Month 5 = (15+17+14)/3 
Month 6 = (17+14+15.33)/3
Month 7 = (14+15.33+15.44)/3

I've been trying to do this using a recursive CTE:

;WITH cte([month],forecast) AS (
    SELECT 1,CAST(10 AS DECIMAL(28,2))
    UNION ALL
    SELECT 2,CAST(15 AS DECIMAL(28,2))
    UNION ALL 
    SELECT 3,CAST(17 AS DECIMAL(28,2))
    UNION ALL
    SELECT
        [month]=[month]+1,
        forecast=CAST(AVG(forecast) OVER (ORDER BY [month] ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING) AS DECIMAL(28,2))
    FROM
        cte
    WHERE
        [month]<=12
)
SELECT * FROM cte WHERE month<=12;

Fiddle: http://sqlfiddle.com/#!6/9ac4a/3

But it doesn't work as expected, as It returns the following result:

| month | forecast |
|-------|----------|
|     1 |       10 |
|     2 |       15 |
|     3 |       17 |
|     4 |   (null) |
|     5 |   (null) |
|     6 |   (null) |
|     7 |   (null) |
|     8 |   (null) |
|     9 |   (null) |
|    10 |   (null) |
|    11 |   (null) |
|    12 |   (null) |
|     3 |   (null) |
|     4 |   (null) |
|     5 |   (null) |
|     6 |   (null) |
|     7 |   (null) |
|     8 |   (null) |
|     9 |   (null) |
|    10 |   (null) |
|    11 |   (null) |
|    12 |   (null) |
|     2 |   (null) |
|     3 |   (null) |
|     4 |   (null) |
|     5 |   (null) |
|     6 |   (null) |
|     7 |   (null) |
|     8 |   (null) |
|     9 |   (null) |
|    10 |   (null) |
|    11 |   (null) |
|    12 |   (null) |

Expected output:

| month | forecast |
|-------|----------|
|     1 |       10 |
|     2 |       15 |
|     3 |       17 |
|     4 |    14.00 |
|     5 |    15.33 |
|     6 |    15.44 |
|     7 |    14.93 |
|     8 |    15.23 |
|     9 |    15.20 |
|    10 |    15.12 |
|    11 |    15.18 |
|    12 |    15.17 |

Can someone tell me what's wrong with this query?

Expected output is the first table in my question: Month and forecast. So basically I have values (Actuals) only for the first 3 months, for months>3 I need to forecast the value, as the average of last 3 values. — Haytem BrB, Feb 02 '16 at 09:29

score 6 · Accepted Answer · answered Feb 02 '16 at 11:25

I propose something like this:

WITH T AS
(
    SELECT 1 AS [month], CAST(10 AS DECIMAL(28,2)) AS [forecast], CAST(-5 AS DECIMAL(28,2)) AS three_months_ago_forecast, CAST(9 AS decimal(28,2)) AS two_months_ago_forecast, CAST(26 AS decimal(28,2)) as one_month_ago_forecast
    UNION ALL
    SELECT 2,CAST(15 AS DECIMAL(28,2)), CAST(9 AS decimal(28,2)), CAST(26 AS decimal(28,2)), CAST(10 AS DECIMAL(28,2))
    UNION ALL 
    SELECT 3,CAST(17 AS DECIMAL(28,2)), CAST(26 AS decimal(28,2)), CAST(10 AS DECIMAL(28,2)), CAST(15 AS DECIMAL(28,2))
),
LT AS -- LastForecast
(
    SELECT *
    FROM T
    WHERE [month] = 3
),
FF AS -- Future Forecast
(
    SELECT *
    FROM LT

    UNION ALL

    SELECT 
        FF.[month] + 1 AS [month], 
        CAST( (FF.forecast * 4 - FF.three_months_ago_forecast) / 3 AS decimal(28,2)) AS forecast,
        FF.two_months_ago_forecast as three_months_ago_forecast,
        FF.one_month_ago_forecast as two_months_ago_forecast,
        FF.forecast as one_month_ago_forecast
    FROM FF
    WHERE
        FF.[month] < 12

)
SELECT * FROM T
WHERE [month] < 3
UNION ALL
SELECT * FROM FF

Output:

+-------+----------+---------------------------+-------------------------+------------------------+
| month | forecast | three_months_ago_forecast | two_months_ago_forecast | one_month_ago_forecast |
+-------+----------+---------------------------+-------------------------+------------------------+
|     1 | 10.00    | -5.00                     | 9.00                    | 26.00                  |
|     2 | 15.00    | 9.00                      | 26.00                   | 10.00                  |
|     3 | 17.00    | 26.00                     | 10.00                   | 15.00                  |
|     4 | 14.00    | 10.00                     | 15.00                   | 17.00                  |
|     5 | 15.33    | 15.00                     | 17.00                   | 14.00                  |
|     6 | 15.44    | 17.00                     | 14.00                   | 15.33                  |
|     7 | 14.92    | 14.00                     | 15.33                   | 15.44                  |
|     8 | 15.23    | 15.33                     | 15.44                   | 14.92                  |
|     9 | 15.20    | 15.44                     | 14.92                   | 15.23                  |
|    10 | 15.12    | 14.92                     | 15.23                   | 15.20                  |
|    11 | 15.19    | 15.23                     | 15.20                   | 15.12                  |
|    12 | 15.18    | 15.20                     | 15.12                   | 15.19                  |
+-------+----------+---------------------------+-------------------------+------------------------+

Pரதீப் · Answer 2 · 2016-02-02T10:10:45.347

2

Try this

WITH cte
     AS (SELECT *
         FROM   (VALUES (1,10,NULL),
                        (2,15,NULL),
                        (3,17,NULL),
                        (4,NULL,14.00),
                        (5,NULL,15.33),
                        (6,NULL,15.44),
                        (7,NULL,14.93)) tc (month, act, fore))
SELECT mon,avg(res) 
FROM   cte a
       CROSS apply (SELECT TOP 3 ( COALESCE(a.act, a.fore) ) AS res,
                                 b.month                     AS mon
                    FROM   cte b
                    WHERE  a.month < b.month
                    ORDER  BY a.month DESC) cs
GROUP  BY mon
ORDER  BY mon

or in Sql Server 2012+ use this

SELECT
    [month]=[month]+1,
    forecast=CAST(AVG(COALESCE(act,fore)) OVER (ORDER BY [month] ROWS BETWEEN 3 PRECEDING  AND CURRENT row  ) AS DECIMAL(28,2))
FROM
    cte

edited Feb 02 '16 at 10:10

answered Feb 02 '16 at 09:52

Pரதீப்

91,748
19
131
172

Thanks @VR46, can you tell me what is wrong with my query? as this is my question actually. – Haytem BrB Feb 02 '16 at 09:56
Msg 8120, Level 16, State 1, Line 1 Column 'cte.month' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. – Jesús López Feb 02 '16 at 10:05
@JesúsLópez - check now – Pரதீப் Feb 02 '16 at 10:07
It doesn't thow an error now, but the query doesn't output the expected result – Jesús López Feb 02 '16 at 10:09
The first version needs a TALLY table for more months (prints out 7 months only). The second version has an error, should be `BETWEEN 2 PRECEDING AND CURRENT ROW` (and prints out 8 months) – TT. Feb 02 '16 at 10:19
@JesúsLópez - I don't think so – Pரதீப் Feb 02 '16 at 10:24
@haytem - First of all you don't need recursive CTE to do this. Then you need to use `ROWS BETWEEN 3 PRECEDING AND CURRENT row` in window frame – Pரதீப் Feb 02 '16 at 10:25
@VR46. Obviously it doesn't, it doesn't show forecasts for months that there are not on the table. there are only three rows Initially, but the expected output has 12 rows – Jesús López Feb 02 '16 at 10:36
@JesúsLópez - initially that is not the expected result – Pரதீப் Feb 02 '16 at 11:06

Calculate forecast average using recursive CTE

2 Answers2

Linked