Definition

Purpose

The problem model provides problem-list records for patients included in recruit studies from the anon extract. The view is scoped to fully anonymised patients via the included patients view.

Information

The Problem model provides problem-list records for patients included in recruit studies. It captures the key identifiers and foreign key relationships linking problems to patients, organisations, user roles, and potentially parent problems. All patient identifiers (patient_id, nhs_number, registration_id) are pseudonymised through the recruit included patients view.

Each problem record is uniquely identified within a specific healthcare organisation.

• emis_observation_guid (PK): The unique identifier for the problem record
• pseudo_registration_guid (FK): Hashed registration identifier - links to patient
• pseudo_patient_id (FK): Hashed patient identifier - links to patient
• emis_registration_organisation_guid (FK): Links to organisation
• emis_parent_observation_guid (FK): Links to parent problem (self-link)
• emis_last_review_userinrole_guid (FK): Links to user_in_role
• study_id (FK): Links to user_studies

Examples

Get all problems for a study

SELECT *
FROM hive.explorer_recruit_anon.problem
WHERE study_id = 'STUDY001'
LIMIT 100;

Find active problems for an organisation

SELECT *
FROM hive.explorer_recruit_anon.problem
WHERE end_date IS NULL
  AND organisation = 'CDB-0001'
LIMIT 100;

Find active problems with significance

SELECT
  emis_observation_guid,
  patient_id,
  problem_status_description,
  fhir_significance
FROM hive.explorer_recruit_anon.problem
WHERE end_date IS NULL
  AND organisation = 'CDB-0001'
LIMIT 100;