Definitions / clinician_sick_full_list
Clinician off sick with a full list
A clinician who has reported sick for today while at least six patients are still booked with them for the rest of the day.
Unit: clinicians · parameters: min bookings 6 · also said as: sick clinician, clinician off sick, doctor off sick, sick with a full list, clinician absent, cover needed
Operations
Head of operations (operations) · version 1, changed 2026-11-02
A sick absence for today reported by now, and at least min_bookings live appointments with that clinician starting after now. The evidence counts the free slots same-specialty colleagues at the same clinic have left today, which decides between moving the list and cancelling it.
First version.
WITH sick AS (
SELECT ab.clinician_id, min(ab.reported_at) AS reported_at FROM absences ab
WHERE ab.kind = 'sick' AND ab.reported_at <= {now} AND ab.day = CAST({now} AS DATE)
GROUP BY ab.clinician_id
),
live AS (
SELECT a.* FROM appointments a
WHERE a.start_at > {now} AND CAST(a.start_at AS DATE) = CAST({now} AS DATE)
AND a.booked_at <= {now} AND (a.cancelled_at IS NULL OR a.cancelled_at > {now})
),
remaining AS (
SELECT l.clinician_id, count(*) AS bookings, list(l.id ORDER BY l.start_at) AS appointment_ids,
min(l.start_at) AS first_start
FROM live l JOIN sick s ON s.clinician_id = l.clinician_id
GROUP BY l.clinician_id HAVING count(*) >= {min_bookings}
),
cover AS (
SELECT k.id AS sick_id, s.clinician_id AS colleague_id,
sum(greatest(0, floor(date_diff('minute', greatest(s.start_at, {now}), s.end_at) / s.slot_minutes)))
- (SELECT count(*) FROM live y WHERE y.clinician_id = s.clinician_id) AS free_slots
FROM clinicians k
JOIN clinicians c2 ON c2.clinic_id = k.clinic_id AND c2.specialty_id = k.specialty_id AND c2.id <> k.id
JOIN sessions s ON s.clinician_id = c2.id AND s.day = CAST({now} AS DATE) AND s.status = 'planned'
AND s.end_at > {now} AND (s.cancelled_at IS NULL OR s.cancelled_at > {now})
WHERE k.id IN (SELECT clinician_id FROM remaining)
AND c2.id NOT IN (SELECT clinician_id FROM sick)
GROUP BY k.id, s.clinician_id
),
best AS (
SELECT sick_id, sum(greatest(free_slots, 0)) AS cover_free_slots,
list(colleague_id ORDER BY free_slots DESC) FILTER (WHERE free_slots > 0) AS cover_clinician_ids
FROM cover GROUP BY sick_id
)
SELECT 'clinician_day' AS subject_type, r.clinician_id || ':' || strftime({now}, '%Y-%m-%d') AS subject_id,
r.clinician_id, k.clinic_id, s.reported_at AS started_at, 'high' AS severity,
json_object(
'clinician', k.name, 'specialty', sp.name, 'clinic', c.name, 'reported_at', s.reported_at,
'bookings', r.bookings, 'appointment_ids', r.appointment_ids, 'first_start', r.first_start,
'cover_free_slots', coalesce(b.cover_free_slots, 0), 'cover_clinician_ids', coalesce(b.cover_clinician_ids, [])
) AS evidence
FROM remaining r
JOIN sick s ON s.clinician_id = r.clinician_id
JOIN clinicians k ON k.id = r.clinician_id
JOIN specialties sp ON sp.id = k.specialty_id
JOIN clinics c ON c.id = k.clinic_id
LEFT JOIN best b ON b.sick_id = r.clinician_id
ORDER BY started_at, r.clinician_id
Tests: count_between, unique, monday_morning