Definition

The Appointment Session User model provides a view of the relationships between appointment sessions and GP practice staff. It captures the many-to-many mapping between appointment sessions and healthcare staff users.

Information

The data model maintains the linkage between appointment sessions and users, allowing users to track which healthcare professionals were involved in specific appointment interactions.

This model contains the following key information for appointment session users:

• Record and user identifiers

• Linking fields to organisation and location

Grain and Scope

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

This model includes the following key identifiers:

• session_id: The unique internal identifier for the appointment session record within an organisation.
• session_guid: The GUID for the appointment session record within an organisation.
• session_uuid: The UUID derived from session_id and organisation, providing a stable unique identifier.
• user_in_role_id: The unique internal identifier for the session user record within an organisation.
• user_in_role_guid: The GUID for the session user record within an organisation.
• user_in_role_uuid: The UUID derived from user_in_role_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 session user 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["Data Collection"]
        n10["Session Identifier"]
        n11["User Information"]
        n12["Organisation Data"]
    end

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

    n7["User Session 123"] --> container
    n5["User Session 98"] --> container
    n6["User Session 456"] --> container
    n4["User Session 20"] --> container
    n8["User Session 47"] --> container

    container --> n13["Gather User Sessions"]
    n13 --> |"Unique Session IDs"|n16["123, 98, 456, 20, 47"]
    n16 --> n14["ETL"]
    n14 --> n15["Appointment Session User Model"]

    n19["User Roles"] --> n14
    n20["Appointment Data"] --> n14

    n7["User Session 123"]:::rect
    n5["User Session 98"]:::rect
    n6["User Session 456"]:::rect
    n4["User Session 20"]:::rect
    n8["User Session 47"]:::rect

    n10:::rect
    n11:::rect
    n12:::rect
    n13:::extract
    n14:::event
    n15:::database
    n16:::rect
    n19:::rect
    n20:::rect

    classDef rect rect
    classDef extract circle
    classDef event path
    classDef database cylinder

Examples

Users for a specific session

SELECT
  emis_session_id,
  emis_session_userinrole_id,
  organisation,
  transform_datetime,
  _execution_date
FROM
  hive.explorer_ipcv_vanilla.appointment_session_user_v2
WHERE
  emis_session_id = 289
  AND organisation = 'CDB-70603'
  AND NOT is_deleted;

Sessions attended per user in the last month

SELECT
  emis_session_userinrole_id,
  organisation,
  COUNT(DISTINCT emis_session_id) AS total_sessions
FROM
  hive.explorer_ipcv_vanilla.appointment_session_user_v2
WHERE
  transform_datetime > DATE_ADD('month', -1, CURRENT_TIMESTAMP)
  AND NOT is_deleted
GROUP BY
  emis_session_userinrole_id,
  organisation
ORDER BY
  total_sessions DESC;

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_user_v2 AS session_user
  LEFT JOIN hive.explorer_ipcv_vanilla.appointment_session_v2 AS session 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;