Definition

The Appointment Slot data model provides information about patient appointments. Contains information about the type, status and timings of the appointment

Constraints and Notes

• Soft deletion : When is_deleted is true, data fields will be nullified in upstream transformations and should be excluded from most analytical queries. Primary keys and foreign keys to patient data are preserved so deleted records and their relationships can still be identified. Foreign keys to coding tables are nullified as the coded information is no longer valid.

Examples

Active booked appointments in the next 7 days

SELECT
  slot_id,
  patient_id,
  slot_start_datetime,
  slot_end_datetime,
  current_status_description,
  slot_type_description,
  organisation
FROM explorer_open_safely.appointment_slot
WHERE
  NOT is_deleted
  AND slot_start_datetime >= CURRENT_TIMESTAMP
  AND slot_start_datetime < CURRENT_TIMESTAMP + INTERVAL '7' DAY
  AND current_status_description = 'Booked';

Appointments by current status

SELECT
  current_status_type_description,
  current_status_description,
  COUNT(*) AS appointment_count
FROM explorer_open_safely.appointment_slot
WHERE NOT is_deleted
GROUP BY
  current_status_type_description,
  current_status_description
ORDER BY appointment_count DESC;