Andrew Charlwood
40 lines
1.2 KiB
SQL
40 lines
1.2 KiB
SQL
/*
|
|
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";
|