# 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 RunningAgeAverageFROM Forecast_TestWITH 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)