Definition

Purpose

The referral model contains referral 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 Referral model captures referral/request data for patients included in recruit studies. It captures the key identifiers and foreign key relationships linking referrals to patients, consultations, codes, organisations, and other entities. All patient identifiers (patient_id, nhs_number, registration_id) are pseudonymised through the recruit included patients view.

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

• emis_observation_guid (PK): The unique identifier for the referral 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 (from organisation)
• emis_target_organisation_guid (FK): Links to organisation (to organisation)
• emis_authorising_userinrole_guid (FK): Links to user_in_role
• emis_enteredby_userinrole_guid (FK): Links to user_in_role
• emis_problem_observation_guid (FK): Links to problem
• emis_consultation_guid (FK): Links to consultation
• study_id (FK): Links to user_studies

Examples

Get all referrals for a study

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

Find referrals for an organisation

SELECT *
FROM hive.explorer_recruit_anon.referral
WHERE organisation = 'CDB-0001'
LIMIT 100;

Get urgent referrals with service type

SELECT
  emis_observation_guid,
  patient_id,
  emis_urgency_description,
  service_type_requested_description,
  referral_mode_description
FROM hive.explorer_recruit_anon.referral
WHERE emis_urgency_description = 'URGENT'
LIMIT 100;