Definitions / referral_not_booked
Referral not booked
A referral the clinic received and has not booked within the rule: 2 days for an urgent referral, 10 days for a routine one.
Unit: referrals · parameters: urgent days 2, routine days 10, look back days 60 · also said as: unbooked referral, referral not booked, referrals waiting, referral never booked, lost referral
Booking
Head of booking (booking) · version 1, changed 2026-11-02
Sent at least the rule's days ago, no booking recorded by now. Referrals for a record that was merged into another are counted on the surviving record only. Whether a booking invite went out (and was not answered) is in the evidence.
First version.
SELECT 'referral' AS subject_type, r.id AS subject_id, r.id AS referral_id, r.patient_id, r.clinic_id,
r.specialty_id,
r.sent_at + to_days(CASE r.priority WHEN 'urgent' THEN {urgent_days} ELSE {routine_days} END) AS started_at,
CASE r.priority WHEN 'urgent' THEN 'high' ELSE 'medium' END AS severity,
json_object(
'priority', r.priority, 'sent_at', r.sent_at,
'rule_days', CASE r.priority WHEN 'urgent' THEN {urgent_days} ELSE {routine_days} END,
'days_waiting', round(date_diff('hour', r.sent_at, {now}) / 24.0, 1),
'invite_sent_at', CASE WHEN r.invite_sent_at <= {now} THEN r.invite_sent_at END,
'invite_unanswered', coalesce(r.invite_sent_at <= {now}, FALSE),
'specialty', s.name, 'clinic', c.name, 'referred_by', k.name
) AS evidence
FROM referrals r
JOIN specialties s ON s.id = r.specialty_id
JOIN clinics c ON c.id = r.clinic_id
LEFT JOIN clinicians k ON k.id = r.from_clinician_id
JOIN patients p ON p.id = r.patient_id
WHERE r.sent_at <= {now} AND r.sent_at >= {now} - INTERVAL ({look_back_days}) DAY
AND (r.booked_at IS NULL OR r.booked_at > {now})
AND r.sent_at + to_days(CASE r.priority WHEN 'urgent' THEN {urgent_days} ELSE {routine_days} END) <= {now}
AND NOT (p.merged_into IS NOT NULL AND p.merged_at <= {now})
ORDER BY started_at, r.id
Tests: count_between, unique