Definition

The MKB Mapping DMD Preparation model maps EMIS medication preparation code identifiers to their corresponding NHS Dictionary of Medicines and Devices (dm+d) product code identifiers, enabling consistent cross-referencing between EMIS coding and the national dm+d standard.

Information

The model provides a direct linkage between EMIS preparation codes and dm+d product codes, with each record also carrying the associated SNOMED CT description identifier. This allows downstream consumers to resolve EMIS-specific medication codes to nationally recognised dm+d product identifiers and their SNOMED CT representations, supporting interoperability and medication analysis across participating organisations.

Grain and Scope

Each row is a unique mapping dm+d preparation record and each record is uniquely identified by preparation_code_id column.

This model includes the following key identifiers:

• preparation_code_id: The unique EMIS identifier for the drug code.

The following fields are important for tracking data lineage and freshness:

• is_deleted: Indicates whether the mapping dm+d preparation record has been deleted at source.

• transform_datetime: The timestamp indicating when the record was last processed and updated in the data model. This field is crucial for understanding the current state of the data.

Things to be aware of

• Mapping dm+d Preparation Updates: dm+d mappings to EMIS preparation codes can change over time; EMIS MKB is updated monthly and may include relevant updates.

Examples

Find codes from DMD product code

SELECT
  *
FROM
  hive.explorer_ipcv_vanilla.mkb_mapping_dmdpreparation_v2
WHERE
  snomed_concept_id = 4205211000001100;

List of DMD drugs with price per unit under 1

SELECT
  dc.emis_term AS drug_name,
  dmd.snomed_concept_id,
  dmd.drug_preparation_code_id,
  dc.price_per_unit
FROM
  hive.explorer_ipcv_apollo.mkb_mapping_dmdpreparation_v2 AS dmd
  JOIN hive.explorer_ipcv_apollo.mkb_drugpack_v2 AS dc ON dc.dmd_productcode_id = dmd.snomed_concept_id
WHERE
  dc.is_deleted = FALSE
  AND dc.price_per_unit < 1
GROUP BY
  dc.emis_term,
  dmd.snomed_concept_id,
  dmd.drug_preparation_code_id,
  dc.price_per_unit;