Definition

The Appointment Session data model provides a view of appointment session data associated with the healthcare organisation. These sessions may or may not be available for patients to book and can also represent other activities, such as meetings or walk-in clinics, beyond general patient appointments.

Information

The Appointment Session data model contains the following key information for appointment sessions:

• Record identifiers

• Session and system dates

• Session classification about category, type, private, etc.

• Slot capacity and booking status metrics

• Appointment attendance, cancellation, and booking metrics

• In-person and virtual appointment counts

• Registered and unregistered patient counts

Appointment Session Metrics

The Appointment Session model contains derived metrics describing session capacity and appointment activity:

• total_slots: The total number of slots configured for the session.
• bookable_slots: The number of slots available to be booked.
• booked_slots: The number of slots that have been booked.
• unfilled_slots: The number of bookable slots that remain unfilled.
• deleted_slots and embargoed_slots: The number of slots that are deleted or not yet available to book.
• dna_appointments: The number of appointments that were not attended.
• cancelled_and_rebooked_appointments and cancelled_and_not_rebooked_appointments: The number of cancelled appointments grouped by whether they were subsequently rebooked.
• same_day_appointments: The number of appointments booked on the same day as the appointment.
• in_person_appointments and virtual_appointments: The number of appointments delivered in person or virtually.
• registered_patient_count and unregistered_patient_count: The number of appointments booked by registered and unregistered patients.

Grain and Scope

Each row represents a unique appointment session record and each record is uniquely identified by either combining session_id and organisation or the session_uuid column.

This model also includes the following key session identifiers:

• session_id: The unique internal identifier for the session record within an organisation.
• session_guid: The GUID for the session record within an organisation.
• session_uuid: The UUID derived from session_id and organisation, providing a stable unique identifier.

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

• is_deleted: Indicates whether the session 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.

Things to be aware of

• Certain session types from EMIS-sourced data are excluded: Session types that indicate the organisation practice is closed or that the session has been re-allocated for that time, are excluded from the dataset as the organisation would be closed to appointments during that period.

Overview

flowchart TD
    n1["Organisation 1"] --> n2["Session A"]
    n1 --> n3["Session B"]
    n1 --> n4["Location X"]

    n5["Organisation 2"] --> n6["Session C"]
    n5 --> n7["Location Y"]

    n8["Organisation 3"] --> n9["Session D"]
    n8 --> n10["Location Z"]

    n2 --> n11["Gather Session Data"]
    n3 --> n11
    n4 --> n11
    n6 --> n11
    n7 --> n11
    n9 --> n11
    n10 --> n11
    n11 --> |"Unique Session IDs"|n12["ETL"]
    n12 --> |"srv_appointment_session"|n13["Final Consolidated Data"]

    classDef nodeStyle stroke:#9961a4;
    class n1,n2,n3,n4,n5,n6,n7,n8,n9,n10,n11,n12,n13 nodeStyle;

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

Examples

Currently active sessions

SELECT
  emis_session_id,
  emis_session_guid,
  session_description,
  session_start_date,
  session_start_time,
  session_end_date,
  session_end_time,
  organisation,
  session_type_description,
  session_category_display_name,
  emis_location_guid
FROM
  hive.explorer_ipcv_vanilla.appointment_session_v2
WHERE
  CURRENT_DATE BETWEEN session_start_date AND session_end_date
  AND NOT is_deleted;

Last month sessions

SELECT
  emis_session_id,
  emis_session_guid,
  session_description,
  session_start_date,
  session_start_time,
  session_end_date,
  session_end_time,
  organisation,
  session_type_description,
  session_category_display_name,
  emis_location_guid
FROM
  hive.explorer_ipcv_vanilla.appointment_session_v2
WHERE
  session_start_date BETWEEN DATE_TRUNC('MONTH', CURRENT_DATE - INTERVAL '1' MONTH) AND DATE_TRUNC('MONTH', CURRENT_DATE)  - INTERVAL '1' DAY
  AND NOT is_deleted;

Session user details

SELECT
  user_in_role.given_name || ' ' || user_in_role.surname AS user_name,
  session.emis_session_id,
  session.emis_session_guid,
  session.session_category_display_name,
  session.session_type_description
FROM
  hive.explorer_ipcv_vanilla.appointment_session_v2 AS session
  LEFT JOIN hive.explorer_ipcv_vanilla.appointment_session_user_v2 AS session_user ON session.emis_session_id = session_user.emis_session_id
  AND session.organisation = session_user.organisation
  LEFT JOIN hive.explorer_ipcv_vanilla.user_in_role_v2 AS user_in_role ON user_in_role.emis_user_id = session_user.emis_session_userinrole_id
  AND user_in_role.organisation = session_user.organisation;