/*
    Rolling period generator
    ========================

    Purpose:
    Generate reusable rolling reporting windows for monthly outputs.

    Use this first when a measure needs:
    - one row per practice per period;
    - a rolling lookback window;
    - stable PeriodStartDate and PeriodEndDate columns for Excel or Power BI.
*/

SET FIRST_PERIOD_END_DATE = '2025-06-30';
SET LAST_PERIOD_END_DATE = LAST_DAY(DATEADD('MONTH', -1, CURRENT_DATE()));
SET LOOKBACK_MONTHS = 12;

WITH RECURSIVE date_periods AS (
    -- Month-end series; cap LAST_PERIOD_END_DATE before sharing a final report.
    SELECT $FIRST_PERIOD_END_DATE::DATE AS "PeriodEndDate"
    UNION ALL
    SELECT LAST_DAY(DATEADD(MONTH, 1, "PeriodEndDate"))::DATE AS "PeriodEndDate"
    FROM date_periods
    WHERE "PeriodEndDate" < $LAST_PERIOD_END_DATE::DATE
),
rolling_periods AS (
    -- Converts each period end into a fixed lookback window.
    SELECT
        DATEADD(MONTH, 1 - $LOOKBACK_MONTHS, DATE_TRUNC('MONTH', "PeriodEndDate"))::DATE AS "PeriodStartDate",
        "PeriodEndDate",
        $LOOKBACK_MONTHS AS "LookbackMonths"
    FROM date_periods
)
SELECT
    "PeriodStartDate",
    "PeriodEndDate",
    "LookbackMonths"
FROM rolling_periods
ORDER BY "PeriodEndDate";