Definition

A comprehensive reference for clinical codes used within healthcare systems. It consolidates code information from multiple coding standards. Similar to the clinical code table but primarily focusing on SNOMED CT mappings for EMIS codes.

Information

The Codeable Concept model serves as a comprehensive reference for clinical codes used within healthcare systems. It consolidates code information from multiple coding standards, primarily focusing on SNOMED CT mappings for EMIS codes.

Each record in the model represents a unique clinical concept identified by a code_id, linked to its corresponding term and related identifiers in other coding systems. The model facilitates standardized data analysis by providing cross-references between different coding systems that may be used across healthcare applications.

Key features of this model include:

• Mapping between proprietary EMIS codes and standard SNOMED CT concepts
• References to fully specified SNOMED CT names

Constraint 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 SNOMED concepts

SELECT
    term,
    snomed_concept_id,
    fully_specified_name,
    readv2_code
FROM hive.explorer_open_safely.codeable_concept
WHERE term LIKE '%diabetes%'
ORDER BY term
LIMIT 50

Get proprietary EMIS codes

SELECT
    code_id,
    term,
    other_code_system,
    other_code,
    other_display
FROM hive.explorer_open_safely.codeable_concept
WHERE other_code_system IS NOT NULL
LIMIT 100

Map between code systems

-- Query showing the relationship between Read v2 and SNOMED CT codes
SELECT
    readv2_code,
    term,
    snomed_concept_id,
    fully_specified_name
FROM hive.explorer_open_safely.codeable_concept
WHERE
    readv2_code IS NOT NULL
    AND snomed_concept_id IS NOT NULL
ORDER BY readv2_code
LIMIT 100