medicines-snowflake/04_rolling_and_pqs/baseline_vs_evaluation_template.sql

/*
    Baseline vs rolling evaluation template
    =======================================

    Purpose:
    Compare a fixed baseline cohort with rolling evaluation periods.

    Typical use:
    - A medicines optimisation search where the baseline is fixed.
    - Later periods check whether the same patients still meet criteria.
    - Output is practice x period, preserving the baseline count.

    Replace medicine_products and the evaluation criteria before use.
*/

SET BASELINE_START = '2025-04-01';
SET BASELINE_END = '2025-06-30';
SET FIRST_EVALUATION_START = '2025-04-01';
SET EVALUATION_MONTHS = 3;
SET BNF_PREFIX = 'REPLACE_WITH_BNF_PREFIX';

WITH latest_prescribing_date AS (
    -- Caps generated periods to data already present in the prescribing table.
    SELECT MAX("DateMedicationStart")::DATE AS "MaxDate"
    FROM REPORTING_DATASETS_ICB.SCRATCHPAD."MEDS__UnifiedPrescribingTable"
    WHERE "DateMedicationStart" <= CURRENT_DATE()
),
periods AS (
    -- Generates candidate month starts; increase ROWCOUNT for longer projects.
    SELECT
        ROW_NUMBER() OVER (ORDER BY SEQ4()) AS "PeriodNumber",
        DATEADD(MONTH, ROW_NUMBER() OVER (ORDER BY SEQ4()) - 1, $FIRST_EVALUATION_START::DATE)::DATE AS "PeriodStartDate"
    FROM TABLE(GENERATOR(ROWCOUNT => 48))
),
evaluation_periods AS (
    SELECT
        "PeriodNumber",
        "PeriodStartDate",
        LAST_DAY(DATEADD(MONTH, $EVALUATION_MONTHS - 1, "PeriodStartDate"))::DATE AS "PeriodEndDate"
    FROM periods
    CROSS JOIN latest_prescribing_date
    WHERE "PeriodStartDate" <= "MaxDate"
),
date_bounds AS (
    SELECT
        $BASELINE_START::DATE AS "MinEventDate",
        GREATEST($BASELINE_END::DATE, COALESCE(MAX("PeriodEndDate"), $BASELINE_END::DATE)) AS "MaxEventDate"
    FROM evaluation_periods
),
practices AS (
    -- Default to active Norfolk and Suffolk parent GP practices.
    SELECT DISTINCT
        "OrganisationCode" AS "PracticeCode",
        "OrganisationName" AS "PracticeName"
    FROM DATA_HUB.DWH."DimOrganisationAndSite"
    WHERE "OrganisationSubType" = 'GP Practice'
      AND "IsSiteActive" = 'Yes'
      AND "IsSiteNorfolkAndSuffolk" = 'Yes'
      AND "SiteCode" = "OrganisationCode"
),
medicine_products AS (
    -- Replace this CTE for VTM, VMP, explicit SNOMED, or cluster-based definitions.
    SELECT DISTINCT "ProductSnomedCode"
    FROM DATA_HUB.DWH."DimMedicineAndDevice"
    WHERE "BNFCode" LIKE $BNF_PREFIX || '%'
),
medicine_events AS (
    -- Pull the smallest event window needed for both baseline and evaluation.
    SELECT DISTINCT
        rx."PersonKey",
        rx."CurrentGeneralPractice" AS "PracticeCode",
        rx."DateMedicationStart"
    FROM REPORTING_DATASETS_ICB.SCRATCHPAD."MEDS__UnifiedPrescribingTable" rx
    CROSS JOIN date_bounds db
    INNER JOIN medicine_products mp
        ON rx."SNOMEDCode" = mp."ProductSnomedCode"
    WHERE rx."PersonKey" IS NOT NULL
      AND rx."CurrentGeneralPractice" IS NOT NULL
      AND rx."DateMedicationStart" BETWEEN db."MinEventDate" AND db."MaxEventDate"
),
baseline_cohort AS (
    -- Fixes each patient to the practice recorded during the baseline window.
    SELECT DISTINCT
        me."PersonKey",
        me."PracticeCode"
    FROM medicine_events me
    WHERE me."DateMedicationStart" BETWEEN $BASELINE_START AND $BASELINE_END
),
baseline_counts AS (
    SELECT
        "PracticeCode",
        COUNT(DISTINCT "PersonKey") AS "BaselineCount"
    FROM baseline_cohort
    GROUP BY "PracticeCode"
),
practice_periods AS (
    -- Ensures every active practice returns one row per evaluation period.
    SELECT
        p."PracticeCode",
        p."PracticeName",
        ep."PeriodNumber",
        ep."PeriodStartDate",
        ep."PeriodEndDate"
    FROM practices p
    CROSS JOIN evaluation_periods ep
),
evaluation_counts AS (
    SELECT
        bc."PracticeCode",
        ep."PeriodNumber",
        ep."PeriodStartDate",
        ep."PeriodEndDate",
        COUNT(DISTINCT me."PersonKey") AS "EvaluationCount"
    FROM baseline_cohort bc
    CROSS JOIN evaluation_periods ep
    LEFT JOIN medicine_events me
        ON me."PersonKey" = bc."PersonKey"
       AND me."DateMedicationStart" BETWEEN ep."PeriodStartDate" AND ep."PeriodEndDate"
    GROUP BY bc."PracticeCode", ep."PeriodNumber", ep."PeriodStartDate", ep."PeriodEndDate"
)
SELECT
    pp."PracticeCode",
    pp."PracticeName",
    pp."PeriodNumber",
    pp."PeriodStartDate",
    pp."PeriodEndDate",
    COALESCE(bc."BaselineCount", 0) AS "BaselineCount",
    COALESCE(ec."EvaluationCount", 0) AS "EvaluationCount",
    COALESCE(bc."BaselineCount", 0) - COALESCE(ec."EvaluationCount", 0) AS "ReductionFromBaseline"
FROM practice_periods pp
LEFT JOIN baseline_counts bc
    ON pp."PracticeCode" = bc."PracticeCode"
LEFT JOIN evaluation_counts ec
    ON pp."PracticeCode" = ec."PracticeCode"
   AND pp."PeriodNumber" = ec."PeriodNumber"
ORDER BY
    pp."PracticeName",
    pp."PeriodNumber";