Show data that takes into consideration consecutive weeks

admin

3 years ago

I have such a situation. I need to only show items that have had 3 consecutive weeks of over or under forecast or Bias.

This is the minimal representative data of what I have now (I cannot trim it any further). I use SSMS.

-- minimal code 
SET ARITHABORT OFF 
SET ANSI_WARNINGS OFF;

WITH Forecast AS
(SELECT [LOC], [DMDUNIT], [STARTDATE], [TOTFCST]
  FROM SCPOMGR.FCSTPERFSTATIC
  WHERE [STARTDATE] >= '2021-11-25'),

ACTUALS AS
(SELECT [LOC], [DMDUNIT], [DMDPostDate], [HistoryQuantity]
  FROM SCPOMGR.HISTWIDE_CHAIN
  WHERE [DMDPostDate] >= '2021-11-25')

SELECT COALESCE(f.[LOC], a.[LOC]) AS 'LOC',  COALESCE(f.[DMDUNIT], a.[DMDUNIT]) AS 'DMDUNIT', COALESCE(f.[STARTDATE], a.[DMDPostDate]) AS 'Start Date',
SUM(F.[TOTFCST]) AS 'Forecast', SUM(A.[HistoryQuantity]) AS 'Actuals', SUM(ABS(A.[HistoryQuantity]) - (f.[TOTFCST])) AS 'Abs Error' 
,(1 - (SUM(A.[HistoryQuantity]) - SUM(F.[TOTFCST])) / SUM(A.[HistoryQuantity])) as 'FA%'
,SUM(A.[HistoryQuantity]) / SUM(F.[TOTFCST]) as 'Bias'


**,CASE WHEN SUM(F.[TOTFCST]) > SUM(A.[HistoryQuantity]) THEN 'Overforecasted' WHEN SUM(F.[TOTFCST]) < SUM(A.[HistoryQuantity]) 
THEN 'Underforecasted (Bias)' ELSE 'No issue' END AS 'Forecasting'**


FROM Forecast F   FULL OUTER JOIN Actuals A
on f.[DMDUNIT]  = a.[DMDUNIT]  and f.[STARTDATE] = a.[DMDPostDate] and f.[LOC] = a.[LOC] 
--WHERE f.[DMDUNIT]='042229215018'
 GROUP BY COALESCE(f.[LOC], a.[LOC]),  COALESCE(f.[DMDUNIT], a.[DMDUNIT]), COALESCE(f.[STARTDATE], a.[DMDPostDate])
 ORDER BY COALESCE(f.[LOC], a.[LOC]),  COALESCE(f.[DMDUNIT], a.[DMDUNIT]), COALESCE(f.[STAR