Andrew Charlwood
50 lines
1.6 KiB
SQL
50 lines
1.6 KiB
SQL
/*
|
|
Medicine reference lookup
|
|
=========================
|
|
|
|
Purpose:
|
|
Search DATA_HUB.DWH."DimMedicineAndDevice" for dm+d products and their
|
|
related BNF, VTM, VMP, AMP, and pack-level identifiers.
|
|
|
|
Common uses:
|
|
- Find the SNOMED product codes to use in a prescribing or dispensing query.
|
|
- Check whether a medicine group is better captured by BNF, VTM, VMP, or
|
|
explicit product codes.
|
|
- Understand how products roll up through the dm+d hierarchy.
|
|
|
|
Replace the SEARCH_TEXT and optional BNF_PREFIX before running.
|
|
*/
|
|
|
|
SET SEARCH_TEXT = 'tirzepatide';
|
|
SET BNF_PREFIX = NULL;
|
|
|
|
SELECT DISTINCT
|
|
med."ProductSnomedCode",
|
|
med."ProductDescription",
|
|
med."ProductLevel",
|
|
med."TherapeuticMoietySnomedCode",
|
|
med."TherapeuticMoietyName",
|
|
med."MedicinalLatestSnomedCode",
|
|
med."ParentPresentationSnomedCode",
|
|
med."BNFCode",
|
|
med."BNFParagraphCode",
|
|
med."RouteName",
|
|
med."StrengthDescription",
|
|
med."PackUnitDescription",
|
|
med."IndicativePricePerUnit",
|
|
med."DrugTariffPricePerUnit"
|
|
FROM DATA_HUB.DWH."DimMedicineAndDevice" med
|
|
WHERE (
|
|
LOWER(med."ProductDescription") LIKE '%' || LOWER($SEARCH_TEXT) || '%'
|
|
OR LOWER(med."TherapeuticMoietyName") LIKE '%' || LOWER($SEARCH_TEXT) || '%'
|
|
OR med."ProductSnomedCode" = $SEARCH_TEXT
|
|
OR med."TherapeuticMoietySnomedCode" = $SEARCH_TEXT
|
|
OR med."MedicinalLatestSnomedCode" = $SEARCH_TEXT
|
|
)
|
|
AND ($BNF_PREFIX IS NULL OR med."BNFCode" LIKE $BNF_PREFIX || '%')
|
|
ORDER BY
|
|
med."TherapeuticMoietyName",
|
|
med."MedicinalLatestSnomedCode",
|
|
med."ProductLevel",
|
|
med."ProductDescription";
|