Definition

The Referral data model represents referrals of a patient for care by an external party or service, or inbound referrals to provide a service related to the patient.

It is a supplemental table to the observation table — observations that are a referral will be present in the referral table with additional fields.

Information

Each record represents a single referral event for a patient, linked to an observation record via referral_observation_id.

Each record can be uniquely identified using the referral_observation_id and organisation.

• is_deleted - Indicates whether the referral has been soft deleted in the source system. All columns excluding primary keys will be NULL if TRUE.
• direction - Indicates whether the referral is inbound (in) or outbound (out).
• urgency / urgency_description - Numeric urgency code and its human readable description (for example 0 = Routine, 2 = Urgent).
• referral_mode_description - Describes how the referral was made (for example Telephone, NHS e-Referral, Written, Verbal).
• service_type_id - An EMIS code ID representing the service to which the patient has been referred.
• transform_datetime - Shows when the record was last made available or changed in the data model.

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