Definition

The clinical code data model provides a reference to original EMISWeb codes for translating a patients clinical information, like observations, diagnosis’ and treatments.

Information

The clinical code model contains EMISWeb code ids and their category description, mapped SNOMED CT codes, as well as other national codes, a variety of standardised coding systems used for different purposes, defined by the NHS

What’s changed in iPCV v2

Schema updates for data maintenance

• The is_deleted column has been added to indicate whether a clinical code has been marked as deleted
• The data_filter and processing_id columns are been removed as it is no longer required

Benefit

• The is_deleted column enables more effective data maintenance by allowing customers to identify and manage deleted clinical codes
• The data model is streamlined by removing deprecated columns from iPCV v1 and v2, resulting in a cleaner and more efficient structure

Customer Action

• Update your ETL processes to use the is_deleted column to manage and clean up clinical codes
• Review your queries and remove any references to the deprecated processing_id and data_filter columns to align with the updated schema and avoid confusion

Examples

Find child code

SELECT
  *
FROM hive.explorer_ipcv.srv_clinical_code
WHERE parent_code_id = 5568191000006118

Distinct EMIS Codes

SELECT
  DISTINCT(emis_code_category_description)
FROM hive.explorer_ipcv.srv_clinical_code
ORDER BY emis_code_category_description ASC

Distinct National Codes for a category

SELECT
DISTINCT national_code,
national_description
FROM hive.explorer_ipcv.srv_clinical_code
WHERE national_code_category = 'PREFERRED COMMUNICATION LANGUAGE'