RoundsCorrowmere Clinics · Estmark synthetic data · seed 23

No-show cascade with a waitlist

A clinic losing several bookings at short notice: at least four slots later today were cancelled in the last two hours and nobody has offered them yet, while patients for that clinic wait on the waitlist.

Unit: clinics · parameters: window hours 2, ahead hours 4, min cancellations 4 · also said as: no-show cascade, cancellation cluster, under-booked clinic, empty slots, gaps in the clinic, clinic under booked, freed slots

Operations

Head of operations (operations) · version 3, changed 2026-11-10

Counts appointments starting in the next ahead_hours (today only) that were cancelled within the last window_hours, not refilled, not already offered to the waitlist, and not on the list of a clinician who reported sick today. No-shows are reported in the evidence but not counted: a slot that has passed cannot be offered again.

A list cancelled because its clinician is off sick is not a cascade: those slots have no clinician to offer them with (clinician_sick_full_list handles it).

WITH freed AS (
  SELECT a.clinic_id, a.id, a.start_at, a.cancelled_at, a.specialty_id
  FROM appointments a
  WHERE a.cancelled_at > {now} - INTERVAL ({window_hours}) HOUR AND a.cancelled_at <= {now}
    AND a.start_at > {now} AND a.start_at <= {now} + INTERVAL ({ahead_hours}) HOUR
    AND CAST(a.start_at AS DATE) = CAST({now} AS DATE)
    AND NOT EXISTS (SELECT 1 FROM offers o WHERE o.appointment_id = a.id AND o.kind = 'waitlist'
                      AND o.created_at <= {now})
    AND NOT EXISTS (SELECT 1 FROM absences s WHERE s.clinician_id = a.clinician_id AND s.kind = 'sick'
                      AND s.day = CAST({now} AS DATE) AND s.reported_at <= {now})
    AND NOT EXISTS (SELECT 1 FROM appointments b WHERE b.clinician_id = a.clinician_id AND b.start_at = a.start_at
                      AND b.id <> a.id AND b.booked_at <= {now}
                      AND (b.cancelled_at IS NULL OR b.cancelled_at > {now}))
),
grouped AS (
  SELECT clinic_id, count(*) AS freed_slots, min(cancelled_at) AS first_cancelled_at,
         list(id ORDER BY start_at) AS appointment_ids, min(start_at) AS first_slot, max(start_at) AS last_slot
  FROM freed GROUP BY clinic_id HAVING count(*) >= {min_cancellations}
),
waiting AS (
  SELECT clinic_id, count(*) AS waiting, list(id ORDER BY priority DESC, added_at) AS waitlist_ids
  FROM waitlist w
  WHERE w.added_at <= {now} AND (w.removed_at IS NULL OR w.removed_at > {now})
  GROUP BY clinic_id
),
no_shows AS (
  SELECT clinic_id, count(*) AS no_shows_today FROM appointments
  WHERE no_show_at <= {now} AND CAST(start_at AS DATE) = CAST({now} AS DATE) GROUP BY clinic_id
)
SELECT 'clinic_day' AS subject_type, g.clinic_id || ':' || strftime({now}, '%Y-%m-%d') AS subject_id,
       g.clinic_id, g.first_cancelled_at AS started_at, 'medium' AS severity,
       json_object(
         'clinic', c.name, 'freed_slots', g.freed_slots, 'appointment_ids', g.appointment_ids,
         'first_slot', g.first_slot, 'last_slot', g.last_slot, 'waiting', w.waiting,
         'waitlist_ids', w.waitlist_ids[1:20], 'no_shows_today', coalesce(n.no_shows_today, 0),
         'window_hours', {window_hours}
       ) AS evidence
FROM grouped g
JOIN waiting w ON w.clinic_id = g.clinic_id
JOIN clinics c ON c.id = g.clinic_id
LEFT JOIN no_shows n ON n.clinic_id = g.clinic_id
ORDER BY started_at, g.clinic_id

Tests: count_between, unique

History: v1 2026-11-02 (Head of operations): First version, counting no-shows too.; v2 2026-11-09 (Head of operations): No-shows no longer count; only cancellations of future slots.