RoundsCorrowmere Clinics · Estmark synthetic data · seed 23

Result filed to the wrong patient

A result filed by hand (the barcode did not scan) to a patient who has no order for that test, while a patient at the same clinic with the family name on the specimen label has that test ordered and still open.

Unit: results · parameters: look back days 14, open order days 14 · also said as: misfiled result, wrong patient, result on the wrong record, misfiled, result filed to wrong patient

Lab

Lab manager (lab) · version 2, changed 2026-11-09

Hand-filed results from the last look_back_days, still on the record they were filed to. The filed patient has no order for the test in the 30 days before; a candidate at the result's clinic has an open order for the test placed in the open_order_days before, the label's family name, and (when the label gives one) the label's birth date, which the filed patient does not share. 'match' says whether the birth date confirms it.

A filed patient with no order is not enough (requests made outside the group have no order); a candidate with an open order and the label's name must exist.

WITH manual AS (
  SELECT r.*, p.dob AS filed_dob, split_part(r.specimen_name, '. ', 2) AS label_family,
         split_part(r.specimen_name, '. ', 1) AS label_initial
  FROM results r JOIN patients p ON p.id = r.filed_patient_id
  WHERE r.filing = 'manual' AND r.resulted_at <= {now}
    AND r.resulted_at >= {now} - INTERVAL ({look_back_days}) DAY
    AND (r.refiled_at IS NULL OR r.refiled_at > {now})
    AND NOT EXISTS (SELECT 1 FROM lab_orders o WHERE o.patient_id = r.filed_patient_id AND o.test_code = r.test_code
                      AND o.ordered_at <= r.resulted_at AND o.ordered_at >= r.resulted_at - INTERVAL 30 DAY)
),
candidates AS (
  SELECT m.id AS result_id, o.patient_id AS candidate_patient_id, o.id AS candidate_order_id, cp.dob,
         left(cp.first_name, 1) = m.label_initial AS initial_match
  FROM manual m
  JOIN lab_orders o ON o.clinic_id = m.clinic_id AND o.test_code = m.test_code
    AND o.ordered_at <= m.resulted_at AND o.ordered_at >= m.resulted_at - INTERVAL ({open_order_days}) DAY
    AND (o.closed_at IS NULL OR o.closed_at > {now}) AND o.patient_id <> m.filed_patient_id
  JOIN patients cp ON cp.id = o.patient_id
  WHERE upper(cp.last_name) = m.label_family
    AND (m.specimen_dob IS NULL OR (m.specimen_dob = cp.dob AND m.specimen_dob <> m.filed_dob))
),
picked AS (
  SELECT result_id, count(*) AS candidates, arg_max(candidate_patient_id, initial_match) AS candidate_patient_id,
         arg_max(candidate_order_id, initial_match) AS candidate_order_id, bool_or(initial_match) AS initial_match
  FROM candidates GROUP BY result_id
)
SELECT 'result' AS subject_type, m.id AS subject_id, m.id AS result_id, m.filed_patient_id,
       p.candidate_patient_id, p.candidate_order_id, m.clinic_id, m.resulted_at AS started_at,
       'high' AS severity,
       json_object(
         'test', t.name, 'specimen_id', m.specimen_id, 'specimen_label', m.specimen_name,
         'specimen_dob', m.specimen_dob, 'request_source', m.request_source,
         'match', CASE WHEN m.specimen_dob IS NOT NULL THEN 'name_and_dob' ELSE 'name_only' END,
         'initial_match', p.initial_match, 'candidates', p.candidates,
         'patient_ids', [m.filed_patient_id, p.candidate_patient_id], 'clinic', c.name
       ) AS evidence
FROM manual m
JOIN picked p ON p.result_id = m.id
JOIN tests t ON t.code = m.test_code
JOIN clinics c ON c.id = m.clinic_id
ORDER BY started_at, m.id

Tests: count_between, unique

History: v1 2026-11-02 (Lab manager): First version: any hand-filed result with no order on the filed record.