-- Snowflake version: Check prescribing by VTM for last 3 months
-- Uses unified PrimaryCareMedication table (combines EMIS + TPP)
-- Dynamically calculates date range from latest data

WITH LatestDate AS (
    SELECT DATEADD(DAY, 1, MAX("DateMedicationStart")::DATE) AS EndDate
    FROM DATA_HUB.PHM."PrimaryCareMedication"
    WHERE "DateMedicationStart" >= DATEADD(MONTH, -6, CURRENT_DATE())
),
DateRange AS (
    SELECT
        EndDate,
        DATEADD(MONTH, -3, EndDate) AS StartDate
    FROM LatestDate
),
SnomedCodes AS (
    SELECT "ProductSnomedCode", "ProductDescription"
    FROM DATA_HUB.DWH."DimMedicineAndDevice"
    WHERE "TherapeuticMoietySnomedCode" = '775477008'  -- Tirzepatide VTM
),
AllPatients AS (
    SELECT DISTINCT pcm."PatientPseudonym"
    FROM DATA_HUB.PHM."PrimaryCareMedication" pcm
    INNER JOIN SnomedCodes sc ON pcm."SNOMEDCode" = sc."ProductSnomedCode"
    CROSS JOIN DateRange dr
    WHERE pcm."DateMedicationStart" > dr.StartDate
        AND pcm."DateMedicationStart" <= dr.EndDate
        AND pcm."PatientPseudonym" IS NOT NULL
)
SELECT
    COUNT(DISTINCT "PatientPseudonym") AS UniquePatientCount,
    (SELECT StartDate FROM DateRange) AS StartDate,
    (SELECT EndDate FROM DateRange) AS EndDate
FROM AllPatients;