Definition

Purpose

The slot model provides appointment slot records from the anon extract. It is patient-scoped where a patient is attached; the view left-joins to the included patients view so study_id is present when the slot belongs to an included patient.

Information

The Slot model provides appointment slot records from the anon extract. It captures the key identifiers and foreign key relationships linking slots to patients, sessions, and organisations. Slots may be patient-scoped where a patient is attached; the study_id is present when the slot belongs to an included patient. All patient identifiers (patient_id, nhs_number, registration_id) are pseudonymised through the recruit included patients view.

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

• emis_appointment_slot_guid (PK): The unique identifier for the slot record
• pseudo_registration_guid (FK): Hashed registration identifier - links to patient (may be null)
• pseudo_patient_id (FK): Hashed patient identifier - links to patient (may be null)
• emis_session_organisation_guid (FK): Links to organisation
• emis_session_guid (FK): Links to session
• study_id (FK): Links to user_studies

Examples

Get all appointment slots for a study

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

Find appointment slots for an organisation

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

Find did-not-attend appointments with duration

SELECT
  emis_appointment_slot_guid,
  patient_id,
  slot_start_date,
  planned_duration_in_minutes,
  did_not_attend_flag
FROM hive.explorer_recruit_anon.current_appointment
WHERE did_not_attend_flag = TRUE
LIMIT 100;