Definition

The Problem data model captures clinical problems recorded against patients in the EMIS Web system and made available for OpenSafely analysis. It represents long-term conditions, diagnoses, and health issues tracked over time, including their status, significance, and hierarchical relationships between problems.

Information

Each record in the Problem model represents a single clinical problem linked to a specific patient and, where applicable, a consultation. Problems can be organised hierarchically through the parent_problem_id field, allowing related conditions to be grouped together.

Each record can be uniquely identified using the observation_id and organisation.

Several fields help characterise the state and context of a problem:

• is_deleted - Indicates whether the problem record has been marked as deleted in the source system.
• problem_status_description - Describes the current clinical status of the problem (e.g. active, past).
• problem_significance_description - Indicates the clinical significance of the problem (e.g. major, minor).
• effective_datetime - The date on which the problem became clinically effective.
• problem_end_datetime - The date on which the problem was resolved or ended, if applicable.
• last_review_datetime - The date the problem was last reviewed by a clinician.
• transform_datetime - Shows when the record was last updated in the data model, which can help determine the currency of the information.

Examples

Active problems for a specific patient

SELECT
  observation_id,
  patient_id,
  consultation_id,
  effective_datetime,
  problem_status_description,
  problem_significance_description,
  snomed_concept_id,
  organisation
FROM explorer_open_safely.problem
WHERE
  patient_id = <patient_id>
  AND organisation = <organisation>
  AND problem_status_description = 'Active'
  AND NOT is_deleted

Major problems opened in the last year

SELECT
  observation_id,
  patient_id,
  effective_datetime,
  problem_status_description,
  problem_significance_description,
  snomed_concept_id,
  organisation
FROM explorer_open_safely.problem
WHERE
  effective_datetime >= CURRENT_TIMESTAMP - INTERVAL '1' YEAR
  AND problem_significance_description = 'Major'
  AND NOT is_deleted