Definition

The Referral data model represents referrals of a patient for care by an external party or service or inbound referrals to provide a services related to the patient. A supplemental table to the observation table where observations that are a referral will be present in the referral table with additional fields.

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

All active outbound referrals for a patient

SELECT
  referral_observation_id,
  patient_id,
  referral_made_datetime,
  ended_datetime,
  direction,
  urgency_description,
  referral_mode_description,
  service_type_id,
  organisation
FROM explorer_open_safely.referral
WHERE
  patient_id = <patient_id>
  AND organisation = <organisation>
  AND direction = 'out'
  AND ended_datetime IS NULL
  AND NOT is_deleted
ORDER BY referral_made_datetime DESC

Urgent or two-week-wait referrals within a date range

SELECT
  referral_observation_id,
  patient_id,
  referral_made_datetime,
  urgency,
  urgency_description,
  service_type_id,
  organisation
FROM explorer_open_safely.referral
WHERE
  urgency_description IN ('Urgent', '2 Week Wait')
  AND referral_made_datetime >= TIMESTAMP '2023-01-01 00:00:00 UTC'
  AND NOT is_deleted
ORDER BY referral_made_datetime DESC