-- Snowflake version: Check prescribing by VTM (Virtual Therapeutic Moiety)
-- Uses unified PrimaryCareMedication table (combines EMIS + TPP)

SET StartDate = '2025-11-01';
SET EndDate = '2025-12-31';

WITH SnomedCodes AS (
    SELECT "ProductSnomedCode", "ProductDescription"
    FROM DATA_HUB.DWH."DimMedicineAndDevice"
    WHERE "TherapeuticMoietySnomedCode" = '41145911000001106'  -- Tirzepatide VTM
)
SELECT DISTINCT
    dos."OrganisationName",
    dp."PersonKey",
    pcm."DateMedicationStart" AS EffectiveDate,
    sc."ProductDescription",
    CAST(pcm."Quantity" AS VARCHAR(20)) AS Quantity,
    pcm."QuantityText" AS Dosage
FROM DATA_HUB.PHM."PrimaryCareMedication" pcm
INNER JOIN DATA_HUB.DWH."DimPerson" dp
    ON pcm."PatientPseudonym" = dp."PatientPseudonym"
INNER JOIN SnomedCodes sc
    ON pcm."SNOMEDCode" = sc."ProductSnomedCode"
INNER JOIN DATA_HUB.DWH."DimOrganisationAndSite" dos
    ON dp."CurrentGeneralPractice" = dos."SiteCode"
WHERE pcm."DateMedicationStart" BETWEEN $StartDate AND $EndDate
    AND pcm."PatientPseudonym" IS NOT NULL
    AND dos."OrganisationSubType" = 'GP Practice'
    AND dos."IsSiteNorfolkAndWaveney" = 'Yes'
    AND dos."IsSiteActive" = 'Yes';