How to generate a Forecast based on last row result in SQL

I need help to develop the proposed task:

I need to count the current month active employees and then generate a “forecast” for the next six months, the forecast is just the last total active from the current month over the next six months, just changing the date value on the result.

Expected Result:

Date_Active	Location	Total_Active	Forecast
2021.04	127	54	No
2021.05	127	54	Yes
2021.04	128	32	No
2021.05	128	32	Yes

So far I just got to count the current month

SELECT
CONCAT(YEAR(@Current),'.',MONTH(@Current)) AS Date_Active,
Emp.Location AS Location,
COUNT(Emp.EmpID) AS Total_Active,
CASE WHEN @Current <= @InitialDate THEN 'No' ELSE 'Yes' END AS Forecast
FROM Employees Emp

I tried to loop it using while, but without success.

WHILE @Current <= @FinalDate
BEGIN
SELECT
CONCAT(YEAR(@Current),'.',MONTH(@Current)) AS Date_Active,
Emp.Location AS Location,
COUNT(Emp.EmpID) AS Total_Active,
CASE WHEN @Current <= @InitialDate THEN 'No' ELSE 'Yes' END AS Forecast
FROM Employees Emp
INNER JOIN 
SET @Current = DATEADD(MONTH, 1, @Current)
END

Assuming @Current is the first day of the given month, @FinalDay is the given month plus six, and @InitialDate is the given date.

It’s my first time here, so sorry for any mistakes.

Used fictional data to simplify due to the original being extremely extense.

EDIT: I’m using SQL Server 2016.

Answer

You can use cross apply:

select dateadd(month, v,n, t.date_active), location, 
       t.total_active, 1 as is_forecast
from (select t.*,
             max(date_active) over (partition by location) as max_date_active
      from t
     ) t cross apply
     (values (1), (2), (3), (4), (5), (6)) v(n)
where max_date_active = date_active

Advertisement

Answer