Andrew Charlwood
80 lines
2.5 KiB
SQL
80 lines
2.5 KiB
SQL
/*
|
|
Practice-level prescribing summary by BNF prefix
|
|
================================================
|
|
|
|
Purpose:
|
|
Produce a compact practice/month summary for prescribing under a BNF prefix.
|
|
|
|
Good first query for:
|
|
- "How much prescribing activity is there for this BNF section?"
|
|
- "Which practices have the most patients/items/cost for this medicine area?"
|
|
- A simple numerator for charts or practice packs.
|
|
*/
|
|
|
|
SET START_DATE = '2025-04-01';
|
|
SET END_DATE = '2026-03-31';
|
|
SET BNF_PREFIX = '0403';
|
|
|
|
WITH practices AS (
|
|
-- Default reporting geography: 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"
|
|
),
|
|
prescribing AS (
|
|
-- Aggregate after joining to the medicine dimension so BNF matching is dm+d-backed.
|
|
SELECT
|
|
DATE_TRUNC('MONTH', rx."DateMedicationStart")::DATE AS "MonthStartDate",
|
|
p."PracticeCode",
|
|
p."PracticeName",
|
|
p."PCNName",
|
|
p."PlaceName",
|
|
p."AllianceName",
|
|
med."BNFParagraphCode",
|
|
COUNT(DISTINCT rx."PersonKey") AS "Patients",
|
|
COUNT(*) AS "PrescriptionRows",
|
|
SUM(TRY_CAST(rx."Quantity" AS FLOAT)) AS "TotalQuantity",
|
|
SUM(rx."EstPrice") AS "EstimatedCost"
|
|
FROM REPORTING_DATASETS_ICB.SCRATCHPAD."MEDS__UnifiedPrescribingTable" rx
|
|
INNER JOIN DATA_HUB.DWH."DimMedicineAndDevice" med
|
|
ON rx."SNOMEDCode" = med."ProductSnomedCode"
|
|
AND med."BNFCode" LIKE $BNF_PREFIX || '%'
|
|
INNER JOIN practices p
|
|
ON rx."CurrentGeneralPractice" = p."PracticeCode"
|
|
WHERE rx."DateMedicationStart" BETWEEN $START_DATE AND $END_DATE
|
|
AND rx."PersonKey" IS NOT NULL
|
|
GROUP BY
|
|
DATE_TRUNC('MONTH', rx."DateMedicationStart")::DATE,
|
|
p."PracticeCode",
|
|
p."PracticeName",
|
|
p."PCNName",
|
|
p."PlaceName",
|
|
p."AllianceName",
|
|
med."BNFParagraphCode"
|
|
)
|
|
SELECT
|
|
"MonthStartDate",
|
|
"PracticeCode",
|
|
"PracticeName",
|
|
"PCNName",
|
|
"PlaceName",
|
|
"AllianceName",
|
|
"BNFParagraphCode",
|
|
"Patients",
|
|
"PrescriptionRows",
|
|
"TotalQuantity",
|
|
"EstimatedCost"
|
|
FROM prescribing
|
|
ORDER BY
|
|
"MonthStartDate",
|
|
"PracticeName",
|
|
"BNFParagraphCode";
|