Andrew Charlwood
72 lines
2.1 KiB
SQL
72 lines
2.1 KiB
SQL
/*
|
|
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";
|