Consecutive Absences

DECLARE @StartDate DATE = {mindate};
DECLARE @EndDate   DATE = {maxdate};

-- Gather all absence days (deduplicated)
;WITH
    UnionAbsences
    AS
    (
            SELECT
                FILEKEY,
                CAST(EVENTDATE AS DATE) AS AbsDate
            FROM EXCEPTIONHIST
            WHERE EXCEPTION = 10
                AND EVENTDATE >= @StartDate
                AND EVENTDATE <= @EndDate

        UNION
            -- eliminates duplicate days
            SELECT
                FILEKEY,
                CAST(EVENTDATE AS DATE) AS AbsDate
            FROM PAYDESHIST
            WHERE PAYDESNUM IN (3, 24, 9, 12, 13, 17, 6)
                AND EVENTDATE >= @StartDate
                AND EVENTDATE <= @EndDate
    ),

-- Calculate consecutive-day group keys
    OrderedAbsences
    AS
    (
        SELECT
            FILEKEY,
            AbsDate,
            ROW_NUMBER() OVER (PARTITION BY FILEKEY ORDER BY AbsDate) AS rn
        FROM UnionAbsences
    ),

    ConsecGroups
    AS
    (
        SELECT
            FILEKEY,
            AbsDate,
            DATEADD(DAY, -ROW_NUMBER() OVER (PARTITION BY FILEKEY ORDER BY AbsDate), AbsDate) AS grp_key
        FROM OrderedAbsences
    ),

-- Find streaks of 3+ consecutive days
    GroupedStreaks
    AS
    (
        SELECT
            FILEKEY,
            MIN(AbsDate) AS StartDate,
            MAX(AbsDate) AS EndDate,
            COUNT(*) AS ConsecutiveDays
        FROM ConsecGroups
        GROUP BY FILEKEY, grp_key
        HAVING COUNT(*) >= 3
    )

-- Summary view (employees + streaks)
SELECT
    e.LASTNAME,
    e.FIRSTNAME,
    e.IDNUM,
    g.StartDate,
    g.EndDate,
    g.ConsecutiveDays
FROM GroupedStreaks AS g
    INNER JOIN EMPLOYEES AS e
    ON e.FILEKEY = g.FILEKEY
    AND e.ACTIVESTATUS = 0
ORDER BY e.LASTNAME, e.FIRSTNAME, g.StartDate;

/*
-- Detailed view (each day in each streak)
SELECT
    e.LASTNAME,
    e.FIRSTNAME,
    e.IDNUM,
    d.AbsDate,
    g.StartDate,
    g.EndDate,
    g.ConsecutiveDays
FROM ConsecGroups AS d
    INNER JOIN GroupedStreaks AS g
    ON g.FILEKEY = d.FILEKEY
        AND d.AbsDate BETWEEN g.StartDate AND g.EndDate
    INNER JOIN EMPLOYEES AS e
    ON e.FILEKEY = d.FILEKEY
    AND e.ACTIVESTATUS = 0
ORDER BY e.LASTNAME, e.FIRSTNAME, d.AbsDate;
*/