Definition

This Sensitive SNOMED Code model provides the SNOMED CT codes that are considered sensitive. This includes data related to areas such as gender, assisted fertilisation, sexually transmitted diseases, and other information deemed confidential.

Constraints and Notes

• Soft deletion: When is_deleted is true, data fields will be nullified in upstream transformations and should be excluded from most analytical queries. Primary keys and foreign keys to patient data are preserved so deleted records and their relationships can still be identified. Foreign keys to coding tables are nullified as the coded information is no longer valid.

Examples

Selecting a concept ID

SELECT
  *
FROM explorer_open_safely.sensitive_snomed_code
WHERE snomed_concept_id = 2007581000006106 -- example code

Finding concept IDs with more than one code ID

SELECT
  snomed_concept_id
FROM explorer_open_safely.sensitive_snomed_code
GROUP BY snomed_concept_id
HAVING count(code_id) > 1

Finding the terms of the example code

SELECT
  ssc.snomed_concept_id,
  cc.fully_specified_name,
  ssc.code_id,
  cc.term
FROM explorer_open_safely.sensitive_snomed_code AS ssc
INNER JOIN  explorer_open_safely.codeable_concept AS cc
ON ssc.code_id = cc.code_id
WHERE ssc.snomed_concept_id = 2007581000006106 -- example code