Definition

The Appointment Slot data model provides a detailed record of individual appointment slots associated with appointment sessions at a GP practice. It captures scheduling information for patient appointments including slot timing, booking status, patient attendance details, embargo rules, and delivery mode, forming the scheduling backbone of patient care activity.

Information

The Appointment Slot data model is a structured representation of individual time slots within an appointment session recorded in EMIS Web. Each slot may be booked, blocked, or available and is associated with a session run by a healthcare organisation. The model enables consumers to understand appointment utilisation, patient attendance, and operational scheduling patterns.

Each appointment slot record is uniquely identified by a combination of slot_id and organisation, ensuring a distinct entry for every slot within a specific healthcare organisation.

This model includes several key identifiers:

slot_id: The unique internal identifier for the appointment slot record.
appointment_slot_guid: The EMIS globally unique identifier for the slot.
slot_uuid: A deterministic UUID generated for cross-system referencing.

The following fields are important for tracking slot status and data lineage:

is_deleted: Indicates whether the slot record has been soft-deleted (v2 only).
is_booked: Indicates whether the slot is currently booked by a patient.
is_blocked: Indicates whether the slot is administratively blocked.
current_status_description: The human-readable status of the slot (e.g. arrived, dna, available).
transform_datetime: The timestamp indicating when the record was last processed in the data warehouse.

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"]

  n7["Session A"]:::rect
  n5["Session B"]:::rect
  n6["Session C"]:::rect
  n4["Session D"]:::rect

Changes in iPCV v2

1. Removal of Columns

Several columns previously available in the appointment slot (v1) table have been removed from the v2 table to align with the broader iPCV v2 data architecture.

Why?

These columns have been removed to streamline the data model and maintain a single source of truth for reference data:

Patient opt-out and registration flag columns: Removed as these flags are maintained in the patient scope dimension table, providing a single source of truth for patient eligibility and registration status.
Session context columns: Session-level attributes such as category, location, and deleted status are now exclusively available through the appointment_session model.
Legacy audit columns: _record_version, slot_deleted, and slot release columns have been replaced by a standardised is_deleted flag and transform_datetime tracking.

Decommissioned Column	Relevant ID / Column	Reason / Extended Data Model to be Referred
`confidential_patient_flag`	`patient_id`	patient_v2
`dummy_patient_flag`	`patient_id`	patient_v2
`regular_patient_flag`	`patient_id`	patient_v2
`sensitive_patient_flag`	`patient_id`	patient_v2
`non_regular_and_current_active`	`patient_id`	patient_v2
`regular_and_current_active`	`patient_id`	patient_v2
`regular_current_active_and_inactive`	`patient_id`	patient_v2
`opt_out_9nd19nu09nu4_flag`	`patient_id`	patient_v2
`opt_out_9nd19nu0_flag`	`patient_id`	patient_v2
`session_category_display_name`	`session_id`	appointment_session_v2
`session_deleted`	`session_id`	appointment_session_v2
`session_location`	`session_id`	appointment_session_v2
`session_start_date_time`	`session_id`	appointment_session_v2
`session_end_date_time`	`session_id`	appointment_session_v2
`session_lead_hcp_type`	`session_id`	appointment_session_v2
`slot_deleted`		Replaced by `is_deleted` in v2
`_record_version`		No longer available in v2
`slot_release_date`		Removed as it is deprecated in v2
`slot_release_date_time`		Removed as it is deprecated in v2
`slot_release_time`		Removed as it is deprecated in v2

Customer benefit

Single source of truth for patient flags through the patient scope dimension table
Reduced risk of data inconsistencies and synchronisation issues
Cleaner separation of session-level and slot-level attributes
Improved data lineage tracking with centralised reference tables

Customer action

Review and update any processes that relied on the removed columns
Query the patient scope dimension table or patient_v2 model for patient registration flags
Use the appointment_session_v2 model for session-level attributes such as location and category
Replace slot_deleted usage with is_deleted for soft-delete filtering

2. NULL value columns

Several columns in the v2 table consistently return NULL and have been retained for backward compatibility.

Why?

These columns are retained to maintain backward compatibility while returning NULL values to ensure consistent behaviour in downstream processes.

Deprecated Column	Replacement ID / Column	Reason / Extended Data Model to be Referred
`emis_consultation_authorising_userinrole_guid`	`patient_id`	Always NULL as it is deprecated in v2
`emis_lead_session_userinrole_guid`		Always NULL as it is deprecated in v2
`emis_bookingsource_organisation_guid`		Always NULL as it is deprecated in v2
`did_not_attend_flag`	`did_not_attend_reason_id`	Always NULL as it is deprecated in v2
`external_patient_flag`	`external_patient_guid`	Always NULL as it is deprecated in v2

Customer benefit

Improved attendance tracking through did_not_attend_reason_id providing structured reason codes
External patient identification now handled via external_patient_guid and external_patient_organisation_id

Customer action

Replace did_not_attend_flag usage with did_not_attend_reason_id for structured DNA reason tracking
Use external_patient_guid to identify external patients instead of external_patient_flag

3. Addition of new columns

The v2 appointment slot model introduces additional columns to enhance auditability, relationships, and scheduling detail.

Soft-delete and tracking columns:

is_deleted
load_datetime
extract_datetime
transform_datetime

UUID and surrogate key columns:

slot_uuid
session_uuid
session_organisation_uuid
patient_uuid
patient_organisation_uuid
external_patient_organisation_uuid

New integer identifier columns:

slot_id
session_id
session_organisation_id
patient_id
patient_organisation_id
external_patient_organisation_id

New relationship and external reference columns:

external_patient_guid
patient_organisation_guid

New detailed scheduling columns:

call_started_datetime
call_ended_datetime
appointment_lead_time_in_minutes
patient_age_at_appointment
current_status_datetime
current_status_type_id
current_status_last_set_by_global_user_in_role_id
current_status_last_set_by_global_user_in_role_uuid
slot_type_id
slot_type_national_category_name
slot_type_national_category_description
context_type
service_setting
booked_by_external_organisation_id
booked_by_external_organisation_uuid
booking_method_id
did_not_attend_reason_id
embargo_type_id
embargo_days
embargo_minutes
embargo_time
embargo_is_relative

Examples

Get all booked slots for a date range

SELECT
  slot_id,
  appointment_slot_guid,
  slot_uuid,
  session_id,
  patient_id,
  slot_start_datetime,
  slot_end_datetime,
  planned_duration_in_minutes,
  current_status_description,
  booking_method_description,
  organisation
FROM hive.explorer_ipcv_vanilla.current_appointment_v2
WHERE
  CAST(slot_start_datetime AS DATE) BETWEEN DATE '2024-01-01' AND DATE '2024-01-31'
  AND is_booked = TRUE
  AND NOT is_deleted

Find did not attend (DNA) appointments

SELECT
  slot_id,
  appointment_slot_guid,
  patient_id,
  slot_start_datetime,
  current_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.slot_id,
  slot.appointment_slot_guid,
  slot.slot_start_datetime,
  slot.slot_end_datetime,
  slot.current_status_description,
  slot.booking_method_description,
  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_id,
  appointment_slot_guid,
  slot_uuid,
  slot_start_datetime,
  slot_end_datetime,
  planned_duration_in_minutes,
  slot_type_description,
  organisation
FROM hive.explorer_ipcv_vanilla.current_appointment_v2
WHERE
  CAST(slot_start_datetime AS DATE) = CURRENT_DATE
  AND is_gp_connect_bookable = TRUE
  AND is_booked = FALSE
  AND is_blocked = FALSE
  AND NOT is_deleted