RoundsCorrowmere Clinics · Estmark synthetic data · seed 23

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