Definition

The appointment session data model contains information about an appointment session, which represents a time period scheduled to book an appointment for a specific day. It includes information such as the start time, end time, category name, and timestamps indicating when the data was extracted and transformed.

Information

The appointment session data model provides a view of appointment session data associated with healthcare organizations in the system. 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.

Each record in this model can be uniquely identified using the session_id and organisation.

In the appointment session model, several indicators are available to distinguish the current status of an appointment session record:

• is_deleted - Indicates whether a session record has been marked as deleted in the source system.
• session_start_date and session_end_date - These fields indicate the start and end dates of the session.
• is_private - Indicates whether a session record has been marked as private, often used for administrative-style sessions.
• transform_datetime - While not directly indicating status, this field shows when the record was last updated in the data model, which can help determine the currency of the status information.

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;

Changes in iPCV v2

Addition of New Columns to the Flavours for Enhanced Data Accuracy

iPCV v2 introduces several new columns across different flavours to improve data accuracy, provide clearer insights into data lineage, and support future enhancements. These columns are included only where relevant to the specific flavour as presented in the schema.

The consolidated list of new columns across all flavours in this data model:

• is_deleted
• session_id
• organisation_id

Why?

These additions provide more granular details for the record. The introduction of new ID-based columns aligns the model with modern identifier practices, paving the way for deprecating older fields and improving system consistency.

Customer benefit

• Enhanced Consistency: Standardizes the use of identifiers and status flags across different model flavours
• Future-Proofing: Adopting new ID-based columns ensures a smoother transition as older identifiers are phased out

Customer action

• Review any logic that handles patient data to incorporate the new fields and IDs for more accurate reporting

• Begin planning the migration from older identifiers (e.g., registration_organisation_guid/ organisation_guid) to their new ID-based counterparts (e.g., registration_organisation_id / organisation_id) in any custom queries or downstream systems

• Validate downstream systems or reports to ensure they can utilize the new columns effectively

• No immediate action required if consuming the fields directly from the v2 model

Examples

Currently active sessions

SELECT
  session_id,
  session_guid,
  session_description,
  session_start_date,
  session_start_time,
  session_end_date,
  session_end_time,
  organisation,
  session_type_description,
  session_category_display_name,
  location_guid
FROM hive.explorer_ipcv.srv_appointment_session
WHERE
  CURRENT_DATE BETWEEN session_start_date AND session_end_date
  AND not is_deleted

Last month sessions

SELECT
  session_id,
  session_guid,
  session_description,
  session_start_date,
  session_start_time,
  session_end_date,
  session_end_time,
  organisation,
  session_type_description,
  session_category_display_name,
  location_guid
FROM hive.explorer_ipcv.srv_appointment_session
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.session_id,
  session.session_guid,
  session.session_category_display_name,
  session.session_type_description
FROM hive.explorer_ipcv.srv_appointment_session AS session
LEFT JOIN hive.explorer_ipcv.srv_appointment_session_user AS session_user
  ON session.session_id = session_user.session_id
  AND session.organisation = session_user.organisation
LEFT JOIN hive.explorer_ipcv.srv_user_in_role AS user_in_role
  ON user_in_role.user_in_role_id = session_user.user_in_role_id
  AND user_in_role.organisation = session_user.organisation