Definition

Purpose

The consultation model (anon extract) exposes consultation/encounter records scoped to studies via the included patients view. It captures identifiers, timestamps, practitioner references and metadata.

Information

The Consultation model exposes consultation/encounter records scoped to studies via the included patients view. It captures the key identifiers and foreign key relationships to related entities. All patient identifiers (patient_id, nhs_number, registration_id) are pseudonymised through the recruit included patients view.

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

• emis_consultation_guid (PK): The unique identifier for the consultation 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_authorising_userinrole_guid (FK): Links to user_in_role
• emis_enteredby_userinrole_guid (FK): Links to user_in_role
• emis_appointment_slot_guid (FK): Links to slot
• study_id (FK): Links to user_studies

Examples

Get all consultations for a study

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

Find confidential consultations

SELECT *
FROM hive.explorer_recruit_anon.encounter
WHERE confidential_flag = TRUE
LIMIT 100;

Get consultations linked to an organisation

SELECT
  emis_consultation_guid,
  patient_id,
  effective_date,
  emis_authorising_userinrole_guid
FROM hive.explorer_recruit_anon.encounter
WHERE organisation = 'CDB-0001'
LIMIT 100;