Definition

Purpose

The clinical_code model extracts clinical code data with identifiers, terms, and descriptions to support healthcare analytics and reporting.

Information

This master table contains the clinical code definitions with their SNOMED and national code mappings. Each code record identifies a unique clinical concept with hierarchical relationships to parent codes for taxonomic browsing.

• emis_code_id (PK): The unique identifier for the clinical code
• snomed_concept_id (FK): Links to the SNOMED concept this code maps to
• snomed_description_id (FK): Links to the SNOMED description for the concept
• parent_emis_code_id (FK): Links to the parent code in the hierarchy

Examples

Get all clinical codes for an organisation

SELECT *
FROM hive.explorer_recruit.clinical_code
WHERE organisation = 'EMIS'
LIMIT 100;

Find clinical codes with a SNOMED mapping

SELECT
  emis_code_id,
  emis_term,
  snomed_concept_id,
  snomed_description_id
FROM hive.explorer_recruit.clinical_code
WHERE snomed_concept_id IS NOT NULL
LIMIT 100;

Get a clinical code and its parent code

SELECT
  child.emis_code_id,
  child.emis_term,
  child.parent_emis_code_id,
  parent.emis_term AS parent_emis_term
FROM hive.explorer_recruit.clinical_code child
LEFT JOIN hive.explorer_recruit.clinical_code parent
  ON child.parent_emis_code_id = parent.emis_code_id
  AND child.organisation = parent.organisation
WHERE child.parent_emis_code_id IS NOT NULL
LIMIT 100;