Definition

DMD Preparation provides the mapping of EMIS preparation code ID for a medication to the NHS Dictionary of Medicines and Devices (dm+d) product code ID (which is also a SNOMED CT concept ID). Similar to the drug code model but primarily focusing on dm+d product codes.

Constraints and Notes

• Soft deletion : When is_deleted is true, data fields may be nullified in upstream transformations and should be excluded from most analytical queries. Primary keys and foreign keys are preserved so deleted linked records can be identified

Examples

Find codes from DMD product code

SELECT
  *
FROM hive.explorer_open_safely.mkb_mapping_dmd_preparation
WHERE dmd_product_code_id = 4205211000001100

List of DMD drugs with price per unit under 1

SELECT
  term AS drug_name,
  dmd.dmd_product_code_id,
  dmd.preparation_code_id,
  price_per_unit
FROM hive.explorer_open_safely.mkb_mapping_dmd_preparation AS dmd
JOIN hive.explorer_open_safely.mkb_drug_pack AS dc
ON dc.dmd_product_code_id = dmd.dmd_product_code_id
WHERE
  dc.is_deleted = FALSE
  AND price_per_unit < 1
GROUP BY
  term,
  dmd.dmd_product_code_id,
  dmd.preparation_code_id,
  price_per_unit