Definition

The Appointment Slot data model represents individual bookable or operational slots within a session, including patient linkage and timing/status changes.

This model supports detailed analysis of booking behaviour, attendance flow, waiting time, lead time, delays, urgency, and booking method flags which provides an insight into appointment utilisation, patient attendance, and operational scheduling patterns.

Information

The Appointment Slot data model is a structured representation of individual time slots within an appointment session recorded in EMIS-sourced data. Each slot may be booked, blocked, or available and is associated with a session run by a healthcare organisation.

This model contains the following key information for appointment slots:

• Record and patient identifiers

• Linking fields to session

• Appointment slot timing lifecycle and system dates

• Appointment slot classification (type, context, service setting)

• Booking and access controls

• Embargo rules

• Performance metrics

Grain and Scope

Each row represents a unique appointment slot record. Each record is uniquely identified by either combining appointment_slot_id and organisation or the appointment_slot_uuid column.

This model includes the following key identifiers:

• appointment_slot_id: The unique internal identifier for the appointment slot record within an organisation.
• appointment_slot_guid: The GUID for the appointment slot record within an organisation.
• appointment_slot_uuid: The UUID derived from appointment_slot_id and organisation, providing a stable unique identifier.

The following fields are important for tracking data lineage and freshness:

• is_deleted: Indicates whether the appointment slot record has been deleted at source.
• transform_datetime: The timestamp indicating when the record was last processed and updated in the data model. This field is crucial for understanding the current state of the data.

Overview

flowchart TB
  subgraph container["Appointment Slot Data"]
    n10["Slot Timing"]
    n11["Booking Status"]
    n12["Patient Attendance"]
    n13["Embargo & Delivery Mode"]
  end

  n17["Organisation 1"] --> n7
  n17["Organisation 1"] --> n5
  n18["Organisation 2"] --> n6
  n18["Organisation 2"] --> n8
  n18["Organisation 2"] --> n4

  n7["Session A"] --> container
  n5["Session B"] --> container
  n6["Session C"] --> container
  n4["Session D"] --> container
  n8["Session E"] --> container

  container --> n13b["Gather Slot Data"]
  n13b --> |"Unique Slot IDs"|n14["ETL"]
  n14 --> n15["Appointment Slot Data Model"]

  classDef nodeStyle stroke:#9961a4;
  class n17,n18,n7,n5,n6,n4,n8,n13b,n14,n15 nodeStyle;

  linkStyle default stroke:#117abf,fill:none;

Examples

Get all booked slots for a date range

SELECT
  appointment_slot_id,
  appointment_slot_guid,
  appointment_slot_uuid,
  session_id,
  emis_patient_id,
  slot_start_date_time,
  slot_end_date_time,
  planned_duration_in_minutes,
  slot_status_description,
  booking_method,
  organisation
FROM
  hive.explorer_ipcv_vanilla.current_appointment_v2
WHERE
  CAST(slot_start_date_time AS DATE) BETWEEN DATE '2020-01-01' AND DATE  '2024-01-31'
  AND slot_booked_flag = TRUE
  AND NOT is_deleted;

Find did not attend (DNA) appointments

SELECT
  appointment_slot_id,
  appointment_slot_guid,
  emis_patient_id,
  slot_start_date_time,
  slot_status_description,
  did_not_attend_reason_id,
  organisation
FROM
  hive.explorer_ipcv_vanilla.current_appointment_v2
WHERE
  did_not_attend_reason_id IS NOT NULL
  AND NOT is_deleted
LIMIT
  100;

Join slots with sessions for full appointment details

SELECT
  slot.appointment_slot_id,
  slot.appointment_slot_guid,
  slot.slot_start_date_time,
  slot.slot_end_date_time,
  slot.slot_status_description,
  slot.booking_method,
  session.session_description,
  session.session_category_display_name,
  session.emis_location_guid
FROM
  hive.explorer_ipcv_vanilla.current_appointment_v2 AS slot
  LEFT JOIN hive.explorer_ipcv_vanilla.appointment_session_v2 AS session ON slot.session_id = session.emis_session_id
  AND slot.organisation = session.organisation
WHERE
  NOT slot.is_deleted
  AND NOT session.is_deleted
LIMIT
  100;

GP Connect bookable slots available today

SELECT
  slot.appointment_slot_id,
  slot.appointment_slot_guid,
  slot.slot_start_date_time,
  slot.slot_end_date_time,
  slot.slot_status_description,
  slot.booking_method,
  session.session_description,
  session.session_category_display_name,
  session.emis_location_guid
FROM
  hive.explorer_ipcv_vanilla.current_appointment_v2 AS slot
  LEFT JOIN hive.explorer_ipcv_vanilla.appointment_session_v2 AS session ON slot.session_id = session.emis_session_id
  AND slot.organisation = session.organisation
WHERE
  NOT slot.is_deleted
  AND NOT session.is_deleted
LIMIT
  100;