RunRate , Forecast using Recursive CTE or UNION ALL

  • imfarhan

    SSC Veteran

    Points: 257

    I would be grateful if you can help me to calculate the RunRate(forecast) on the following table .

    The run rate will be calculate only on the future months on the field 'im_actual' , and the completed months (Past_Months) the value of 'Im_actual' will be same.

    The field 'int_period' keep Fiscal months , so the completed month are , Apr(1) and May(2) where , June(3) onward are future month where we need to calcuatel ForeCast/RunRaate,

    The completed month we don't need to calcuate and the 'Im_actual' values for completed will be same as exist in the table/field(im_actual) ,

    To calculate the Forecast/RR for future months. it should sum of the completed months so in this case will be i.e. (1 and 2) and divide by 2(last completed month), so the calculate valurs for month (3 to 12) will be constant values.

    Similarly, if I wants to calculate the Fore-cast after the month June(3) completed , the future Forecast will be sum of (month [1 to 3] and divided by 3 and this same/constact values will show for furtre moths 4 to 12..

    The dummy data set script i've copied below

    I've tried using recursive sql and UNION by split into two data set

    1st data set simple where the 'im_actual' value not calculated by using

    Also attached my sample

    Create tableForecast_Test
    (
    int_period integer
    ,Division VARCHAR(10)
    ,Cost_Centre integer
    ,im_actual integer);
    INSERT INTO Forecast_Test values (1 , 'A and M', 12345, 1000)
    INSERT INTO Forecast_Test values (2 , 'A and M', 12345, 800)
    INSERT INTO Forecast_Test values (3 , 'A and M', 12345, 600)
    INSERT INTO Forecast_Test values (4 , 'A and M', 12345, 700)
    INSERT INTO Forecast_Test values (5 , 'A and M', 12345, 1200)
    INSERT INTO Forecast_Test values (6 , 'A and M', 12345, 1000)
    INSERT INTO Forecast_Test values (1 , 'A and M', 7777, 500)
    INSERT INTO Forecast_Test values (2 , 'A and M', 7777, 400)
    INSERT INTO Forecast_Test values (3 , 'A and M', 7777, 600)
    INSERT INTO Forecast_Test values (4 , 'A and M', 7777, 700)
    INSERT INTO Forecast_Test values (5 , 'A and M', 7777, 1200)
    INSERT INTO Forecast_Test values (6 , 'A and M', 8888, 1000)

    SELECT
    int_period, Division, Cost_Centre,SUM (im_actual) OVER (ORDER BY int_period) AS RunningAgeTotal,
    --AVG (StudentAge) OVER (ORDER BY Id) AS RunningAgeAverage
    FROM Forecast_Test

    WITH
    cte_main AS
    (
    --Part Two (sum above query)
    SELECT 'Total Completed Month' as QueryNo, 99 as int_period, d1.Division, d1.Cost_Centre,0 as
    im_actual, sum(d1.im_actual) as Sum_Actual
    FROM Forecast_Test d1--inner join Forecast_Test m on m.int_period = d1.int_period and m.Division =
    d1.Division and m.Cost_Centre = d1.Cost_Centrewhere d1.Cost_Centre=12345and d1.int_period <=2group by
    d1.Division, d1.Cost_Centre

    --Part Three (--Future Month replace Sum_Actual)
    UNION ALL
    SELECT d2.int_period, d2.Division, d2.Cost_Centre, d2.im_actual, d1.sum_Actual
    FROM Forecast_Test d2
    inner join Forecast_Test d1 on d2.Cost_Centre= d1.Cost_Centre and d2.Division= d1.Divisionwhere
    d2.Cost_Centre=12345 and d2.int_period >2
    )

    SELECT 'Completed Month' as QueryNo, int_period, Division, Cost_Centre,im_actual, 0 as Sum_ActualFROM Forecast_Testwhere Cost_Centre=12345and int_period <=2UNION ALLselect * from cte_main

    • This topic was modified 3 months ago by  imfarhan. Reason: putting the code in correct format
    • This topic was modified 3 months ago by  imfarhan.
    • This topic was modified 3 months ago by  imfarhan.
    Attachments:
    You must be logged in to view attached files.
  • imfarhan

    SSC Veteran

    Points: 257

    Create table

    Forecast_Test

    (

    int_period integer,

    Division VARCHAR(10),

    Cost_Centre integer,

    im_actual integer

    );

    INSERT INTO Forecast_Test values (1 , 'A and M', 12345, 1000)

    INSERT INTO Forecast_Test values (2 , 'A and M', 12345, 800)

    INSERT INTO Forecast_Test values (3 , 'A and M', 12345, 600)

    INSERT INTO Forecast_Test values (4 , 'A and M', 12345, 700)

    INSERT INTO Forecast_Test values (5 , 'A and M', 12345, 1200)

    INSERT INTO Forecast_Test values (6 , 'A and M', 12345, 1000)

    --

    INSERT INTO Forecast_Test values (1 , 'A and M', 7777, 500)

    INSERT INTO Forecast_Test values (2 , 'A and M', 7777, 400)

    INSERT INTO Forecast_Test values (3 , 'A and M', 7777, 600)

    INSERT INTO Forecast_Test values (4 , 'A and M', 7777, 700)

    INSERT INTO Forecast_Test values (5 , 'A and M', 7777, 1200)

    INSERT INTO Forecast_Test values (6 , 'A and M', 8888, 1000)

    SELECT int_period, Division, Cost_Centre,

    SUM (im_actual) OVER (ORDER BY int_period) AS RunningAgeTotal,

    --AVG (StudentAge) OVER (ORDER BY Id) AS RunningAgeAverage

    FROM Forecast_Test

    [/quote]

    Thanks Again , if you have any question please feel free to ask me

    Many thanks

    Farhan

     

     

    Also ried

    WITH cte_main AS

    (

    --Part Two (sum above query)

    SELECT 'Total Completed Month' as QueryNo, 99 as int_period, d1.Division, d1.Cost_Centre,

    0 as im_actual, sum(d1.im_actual) as Sum_Actual

    FROM Forecast_Test d1

    --inner join Forecast_Test m on m.int_period = d1.int_period and m.Division = d1.Division and m.Cost_Centre = d1.Cost_Centre

    where d1.Cost_Centre=12345

    and d1.int_period <=2

    group by d1.Division, d1.Cost_Centre

    --Part Three (--Future Month replace Sum_Actual)

    UNION ALL

    SELECT d2.int_period, d2.Division, d2.Cost_Centre, d2.im_actual, d1.sum_Actual

    FROM Forecast_Test d2

    inner join Forecast_Test d1 on d2.Cost_Centre= d1.Cost_Centre and d2.Division= d1.Division

    where d2.Cost_Centre=12345 and d2.int_period >2

    )

    SELECT 'Completed Month' as QueryNo, int_period, Division, Cost_Centre,

    im_actual, 0 as Sum_Actual

    FROM Forecast_Test

    where Cost_Centre=12345

    and int_period <=2

    UNION ALL

    select * from cte_main

  • Mr. Brian Gale

    SSC-Insane

    Points: 23170

    My only question with this is a pretty easy one - should your calculation to calculate the Forecast/RR for future months be per-customer, per Cost_Centre, or only look at the int_period?

    Basically, I am just trying to find the proper way to group it across cost centres and across divisions, or if it even needs to be.  It just changes a small part of the math, not a lot.

    I am also not sure I fully understand the math on this, so doing a "best guess" and hoping I am close or at least pointing you in the right direction.  The code does have a "magic number" in it (getting the current month and subtracting 3), but this is  based on you saying that month 1 is April and DATEPART(month,'01-04-2020') will give me 4 as the month is 4.  So subtracting 3 from the current month will give me a number between -2 and 9 (a 12 digit range).  Later when we do the check, we are adding 12 to it if the value is less than 1 (ie 0 becomes 12, -1 becomes 11 and -2 becomes 10) so we can get the full fiscal year.  So if the current calendar month is January, you would get -2.  Your Fiscal month for January should be 10 (as you have 3 months remaining until year end - January, February and March) so the math here works even if it is not that pretty.

    My current guess as to how to tackle this would be something similar to:

    WITH [sourceData]
    AS
    (
    SELECT
    [int_period]
    , [Division]
    , [Cost_Centre]
    , [im_actual]
    , SUM([im_actual]) OVER (PARTITION BY
    [Cost_Centre]
    ORDER BY [int_period]
    ) AS [RunningAgeTotal]
    , DATEPART( MONTH
    , GETDATE()
    ) - 3 AS [currentFiscal]
    FROM [#Forecast_Test]
    )
    , [ForecastRR]
    AS
    (
    SELECT
    [sourceData].[Cost_Centre]
    , (SUM( CASE
    WHEN [sourceData].[int_period] < CASE
    WHEN [sourceData].[currentFiscal] < 1
    THEN [sourceData].[currentFiscal] + 12
    ELSE [sourceData].[currentFiscal]
    END
    THEN [sourceData].[im_actual]
    ELSE 0
    END
    )
    ) / ([sourceData].[currentFiscal] - 1) AS [forecastRR]
    FROM [sourceData]
    GROUP BY [sourceData].[Cost_Centre]
    , [sourceData].[currentFiscal]
    )
    SELECT
    [sourceData].[int_period]
    , [sourceData].[Division]
    , [sourceData].[Cost_Centre]
    , [sourceData].[im_actual]
    , [sourceData].[RunningAgeTotal]
    , [ForecastRR].[forecastRR]
    , CASE
    WHEN [sourceData].[int_period] < [sourceData].[currentFiscal]
    THEN [sourceData].[im_actual]
    ELSE [ForecastRR].[forecastRR]
    END AS [im_Actual_with_ForecastRR]
    FROM [sourceData]
    JOIN [ForecastRR]
    ON [ForecastRR].[Cost_Centre] = [sourceData].[Cost_Centre];

    Is the above close to what you were looking for?

    If not, could you post a sample of what you expect the output to look like in Excel or similar?  This will help me figure out what you expect vs what I provided.

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply