Definitions / result_misfiled
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.