/*
    Current prescribing by VMP
    ==========================

    Purpose:
    Return patient-level prescribing records for all products under one
    Virtual Medicinal Product (VMP).

    Use this when a VTM is too broad and you need a specific formulation,
    strength, or medicinal product family.
*/

SET START_DATE = '2025-04-01';
SET END_DATE = '2026-03-31';
SET VMP_SNOMED_CODE = 'REPLACE_WITH_VMP_SNOMED_CODE';

WITH products AS (
    -- Expands one VMP to all matching product SNOMED codes in the medicine dimension.
    SELECT DISTINCT
        "ProductSnomedCode",
        "ProductDescription",
        "ProductLevel",
        "MedicinalLatestSnomedCode",
        "TherapeuticMoietyName",
        "BNFCode",
        "BNFParagraphCode"
    FROM DATA_HUB.DWH."DimMedicineAndDevice"
    WHERE "MedicinalLatestSnomedCode" = $VMP_SNOMED_CODE
       OR "ProductSnomedCode" = $VMP_SNOMED_CODE
),
practices AS (
    -- Restricts output to active Norfolk and Suffolk parent GP practices.
    SELECT DISTINCT
        "OrganisationCode" AS "PracticeCode",
        "OrganisationName" AS "PracticeName",
        "PCNName",
        "PlaceName",
        "AllianceName"
    FROM DATA_HUB.DWH."DimOrganisationAndSite"
    WHERE "OrganisationSubType" = 'GP Practice'
      AND "IsSiteActive" = 'Yes'
      AND "IsSiteNorfolkAndSuffolk" = 'Yes'
      AND "SiteCode" = "OrganisationCode"
)
SELECT DISTINCT
    p."PracticeCode",
    p."PracticeName",
    p."PCNName",
    p."PlaceName",
    p."AllianceName",
    rx."PersonKey",
    rx."DateMedicationStart",
    prod."ProductDescription",
    prod."ProductLevel",
    prod."TherapeuticMoietyName",
    prod."BNFCode",
    TRY_CAST(rx."Quantity" AS FLOAT) AS "Quantity",
    rx."QuantityUnit",
    rx."Directions",
    rx."SourceSystem"
FROM REPORTING_DATASETS_ICB.SCRATCHPAD."MEDS__UnifiedPrescribingTable" rx
INNER JOIN products prod
    ON rx."SNOMEDCode" = prod."ProductSnomedCode"
INNER JOIN practices p
    ON rx."CurrentGeneralPractice" = p."PracticeCode"
WHERE rx."DateMedicationStart" BETWEEN $START_DATE AND $END_DATE
  AND rx."PersonKey" IS NOT NULL
ORDER BY
    p."PracticeName",
    rx."PersonKey",
    rx."DateMedicationStart";