I have such a situation. I need to only show items that have had 3 consecutive weeks of over or under forecast or Bias.
I am trying to understand how I can i can filter data taking into consideration consecutive weeks.
If someone may have any ideas, I will appreciate…
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'
,ABS((SUM(A.[HistoryQuantity]) - SUM(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'
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.[STARTDATE], a.[DMDPostDate])
