Andrew Charlwood
28 lines
1.2 KiB
SQL
28 lines
1.2 KiB
SQL
-- Snowflake version: Check dispensing by VMP (Virtual Medicinal Product)
|
|
-- Uses GPMeds dispensing data from NATIONAL.GPMED
|
|
|
|
WITH SnomedCodes AS (
|
|
SELECT "ProductSnomedCode"
|
|
FROM DATA_HUB.DWH."DimMedicineAndDevice"
|
|
WHERE "MedicinalLatestSnomedCode" = '40326811000001109' -- Specific VMP
|
|
),
|
|
LatestPeriod AS (
|
|
SELECT MAX("ProcessingPeriodDate") AS MaxPeriodDate
|
|
FROM NATIONAL.GPMED."MedicinesDispensedInPrimarycare"
|
|
)
|
|
SELECT
|
|
gpm."ProcessingPeriodDate",
|
|
dos."OrganisationName",
|
|
COUNT(DISTINCT gpm."PatientPseudonym") AS UniquePatientCount,
|
|
SUM(gpm."ItemCount") AS TotalItemsDispensed,
|
|
SUM(gpm."PaidQuantity") AS TotalQuantityDispensed
|
|
FROM NATIONAL.GPMED."MedicinesDispensedInPrimarycare" gpm
|
|
INNER JOIN SnomedCodes sc ON gpm."PaiddmdCode" = sc."ProductSnomedCode"
|
|
LEFT JOIN DATA_HUB.DWH."DimOrganisationAndSite" dos
|
|
ON gpm."CostCentreODSCode" = dos."SiteCode"
|
|
CROSS JOIN LatestPeriod lp
|
|
WHERE gpm."ProcessingPeriodDate" > DATEADD(MONTH, -12, lp.MaxPeriodDate)
|
|
AND gpm."ProcessingPeriodDate" <= lp.MaxPeriodDate
|
|
GROUP BY gpm."ProcessingPeriodDate", dos."OrganisationName", gpm."CostCentreODSCode"
|
|
ORDER BY gpm."ProcessingPeriodDate", dos."OrganisationName";
|