/*
    Current prescribing by VTM
    =========================

    Purpose:
    Return patient-level prescribing records for all products under one
    Virtual Therapeutic Moiety (VTM).

    Data source:
    REPORTING_DATASETS_ICB.SCRATCHPAD."MEDS__UnifiedPrescribingTable"

    Notes:
    - VTM is useful when you want all products containing the same therapeutic
      moiety, regardless of brand, strength, or pack.
    - The practice CTE is deliberately visible. Change the geography filter
      there if the report should not be Norfolk and Suffolk.
*/

SET START_DATE = '2025-04-01';
SET END_DATE = '2026-03-31';
SET VTM_SNOMED_CODE = 'REPLACE_WITH_VTM_SNOMED_CODE';

WITH products AS (
    -- Expands one VTM to all matching product SNOMED codes in the medicine dimension.
    SELECT DISTINCT
        "ProductSnomedCode",
        "ProductDescription",
        "TherapeuticMoietyName",
        "BNFCode",
        "BNFParagraphCode"
    FROM DATA_HUB.DWH."DimMedicineAndDevice"
    WHERE "TherapeuticMoietySnomedCode" = $VTM_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."TherapeuticMoietyName",
    prod."BNFCode",
    TRY_CAST(rx."Quantity" AS FLOAT) AS "Quantity",
    rx."QuantityUnit",
    rx."Directions",
    rx."IsRepeatPrescription",
    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";