-- Snowflake version: Check prescribing by VMP (Virtual Medicinal Product)
-- Uses unified PrimaryCareMedication table (combines EMIS + TPP)

SET StartDate = '2025-04-01';
SET EndDate = '2025-07-31';

WITH SnomedCodes AS (
    SELECT "ProductSnomedCode"
    FROM DATA_HUB.DWH."DimMedicineAndDevice"
    WHERE "MedicinalLatestSnomedCode" = '40326811000001109'  -- Specific VMP
)
SELECT DISTINCT
    pcm."PatientPseudonym",
    pcm."DateMedicationStart" AS EffectiveDate,
    CAST(pcm."Quantity" AS VARCHAR(20)) AS Quantity
FROM DATA_HUB.PHM."PrimaryCareMedication" pcm
INNER JOIN SnomedCodes sc ON pcm."SNOMEDCode" = sc."ProductSnomedCode"
WHERE pcm."DateMedicationStart" BETWEEN $StartDate AND $EndDate
    AND pcm."PatientPseudonym" IS NOT NULL;