Show data that takes into consideration consecutive weeks

Question

Hello I have such a situation I basically need to write a SQL code for such a statement

select *,
    case when 'Issue' IN ('Overforecasted', 'Underforecasted') AND 'Start Date' DISTINCT 3 dates THEN 'Issue exists for 3 weeks in a row'
    FROM Merged;

I know this is not a proper SQL format but does someone know how it can be edited?

Per one DMDUNIT check if it has 3 issues in column "Issues" and later check if it has 3 different start dates. If it has 3 issues ('Overforecasted', "Underforecasted") and 3 different dates for the same DMDUNIT I need to return it in a new column (end as "3InARow")

The current edited draft

 SET ARITHABORT OFF 
SET ANSI_WARNINGS OFF;


;WITH Forecast AS (
    SELECT LOC, DMDUNIT, STARTDATE, TOTFCST
    FROM SCPOMGR.FCSTPERFSTATIC
    WHERE STARTDATE >= '2021-11-24'
), Actuals AS (
    SELECT LOC, DMDUNIT, DMDPostDate, HistoryQuantity
    FROM SCPOMGR.HISTWIDE_CHAIN
    WHERE DMDPostDate >= '2021-11-24'

), Merged as (
    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 - HistoryQuantity - TOTFCST) / HistoryQuantity as "FA%",
        SUM(a.HistoryQuantity) / SUM(f.TOTFCST) AS "Bias",       
        CASE
            WHEN TOTFCST > HistoryQuantity THEN 'Overforecasted' 
            WHEN TOTFCST < HistoryQuantity THEN 'Underforecasted'
            WHEN HistoryQuantity IS NULL AND TOTFCST > 0 THEN 'Overforecasted'
            WHEN TOTFCST IS NULL AND HistoryQuantity > 0 THEN 'Underforecasted'
            WHEN TOTFCST = 0.000 AND HistoryQuantity IS NULL THEN 'No issue'
        END AS Issue
    FROM Forecast f FULL OUTER JOIN Actuals a
        ON f.LOC = a.LOC AND f.DMDUNIT = a.DMDUNIT AND f.STARTDATE = a.DMDPostDate
         GROUP BY
        COALESCE(f.LOC, a.LOC),
        COALESCE(f.DMDUNIT, a.DMDUNIT),
        COALESCE(f.STARTDATE, a.DMDPostDate),
        a.HistoryQuantity, F.TOTFCST),
    Transitions as (
    select *, 
        case when indicator <> lag(indicator)
            over (partition by DMDUNIT order by "Start Date")
            then 1 end as tripped
    from Merged cross apply (
        select case when Issue in ('Overforecasted', 'Underforecasted') 
            then 1 else 0 end as indicator) v
), Bundles as (
    select *, count(tripped) over (partition by DMDUNIT order by "Start Date") as grp
    from Transitions
), Streaks as (
    select *, count(*) over (partition by DMDUNIT, grp) as cnt
    from Bundles
)
select *, case when indicator = 1 and cnt >= 3 then 'Yes' else 'No' end as InIssueStreak, cnt as StreakLength
from Streaks;

Answer 1

WITH Forecast AS (
    SELECT LOC, DMDUNIT, STARTDATE, TOTFCST
    FROM SCPOMGR.FCSTPERFSTATIC
    WHERE STARTDATE >= '2021-11-24'
), Actuals AS (
    SELECT LOC, DMDUNIT, DMDPostDate, HistoryQuantity
    FROM SCPOMGR.HISTWIDE_CHAIN
    WHERE DMDPostDate >= '2021-11-24'
), Merged AS (
    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'
            WHEN SUM(a.HistoryQuantity) IS NULL AND SUM(f.TOTFCST) > 0 THEN 'Overforecasted'
            WHEN SUM(f.TOTFCST) IS NULL AND SUM(a.HistoryQuantity) > 0 THEN 'Underforecasted'
            WHEN SUM(f.TOTFCST) = 0.000 AND SUM(a.HistoryQuantity) IS NULL THEN 'No issue'
        END AS Issue
    FROM Forecast f FULL OUTER JOIN Actuals a
        ON f.LOC = a.LOC AND f.DMDUNIT = a.DMDUNIT AND f.STARTDATE = a.DMDPostDate
    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)
)
select *,
    case when
        min(Issue) over (
            partition by DMDUNIT order by "Start Date"
            rows between 2 preceding and current row) =
        max(Issue) over (
            partition by DMDUNIT order by "Start Date"
            rows between 2 preceding and current row) and
        count(Issue) over (
            partition by DMDUNIT order by "Start Date"
            rows between 2 preceding and current row) = 3
        then 'Yes' else 'No' end as "3InARow"
from Merged;

If that doesn't work then try gaps and islands:

with (<copied from above...>), Transitions as (
    select *, 
        case when indicator <> lag(indicator)
            over (partition by DMDINIT order by "Start Date")
            then 1 end as tripped
    from Merged cross apply (
        select case when Issue in ('Overforecasted', 'Underforecasted') 
            then 1 else 0 end as indicator) v
), Bundles as (
    select *, sum(tripped) over (partition by DMDUNIT order by "Start Date") as grp
    from Transitions
), Streaks as (
    select *, count(*) over (partition by DMDUNIT, grp) as cnt
    from Bundles
)
select *, case when cnt >= 3 then 'Yes' else 'No' end as InStreak, cnt as StreakLength
from Streaks;

https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=9fcbab1d93b7297aebc340111aa3a448