RoundsCorrowmere Clinics · Estmark synthetic data · seed 23

Abnormal result not acknowledged

A result the lab flagged abnormal or critical that no clinician has acknowledged within the rule's hours (critical: 4 hours, abnormal: 24 hours). The result counts on the record it is filed to as of now.

Unit: results · parameters: abnormal hours 24, critical hours 4, look back days 14 · also said as: unacknowledged result, unacknowledged abnormal, result not acknowledged, abnormal result waiting, results nobody has seen, critical result not seen

Governance

Medical director (clinical governance) · version 1, changed 2026-11-02

Flag abnormal or critical, back from the lab at least the rule's hours ago, and no acknowledgement recorded by now. Results older than look_back_days are left to the monthly audit. The ordering clinician, whether they hold a list today, and the clinic's region (for the on-call doctor) are in the evidence.

First version.

WITH current AS (
  SELECT r.*, CASE WHEN r.refiled_at <= {now} THEN r.refiled_to_patient_id ELSE r.filed_patient_id END AS patient_id,
         CASE WHEN r.flag = 'critical' THEN {critical_hours} ELSE {abnormal_hours} END AS rule_hours
  FROM results r
  WHERE r.flag IN ('abnormal', 'critical') AND r.resulted_at <= {now}
    AND r.resulted_at >= {now} - INTERVAL ({look_back_days}) DAY
    AND (r.acknowledged_at IS NULL OR r.acknowledged_at > {now})
)
SELECT 'result' AS subject_type, c.id AS subject_id, c.id AS result_id, c.patient_id, c.clinic_id,
       c.ordering_clinician_id AS clinician_id, cl.region_id,
       c.resulted_at + to_hours(c.rule_hours) AS started_at,
       CASE WHEN c.flag = 'critical' THEN 'critical' ELSE 'high' END AS severity,
       json_object(
         'test', t.name, 'value', c.value, 'unit', t.unit, 'ref_low', t.ref_low, 'ref_high', t.ref_high,
         'flag', c.flag, 'resulted_at', c.resulted_at, 'rule_hours', c.rule_hours,
         'hours_unacknowledged', round(date_diff('minute', c.resulted_at, {now}) / 60.0, 1),
         'ordering_clinician', k.name, 'clinic', cl.name, 'region', rg.name,
         'clinician_has_list_today', EXISTS (
           SELECT 1 FROM sessions s WHERE s.clinician_id = c.ordering_clinician_id
             AND s.day = CAST({now} AS DATE) AND s.status = 'planned'
             AND (s.cancelled_at IS NULL OR s.cancelled_at > {now}))
       ) AS evidence
FROM current c
JOIN tests t ON t.code = c.test_code
JOIN clinics cl ON cl.id = c.clinic_id
JOIN regions rg ON rg.id = cl.region_id
LEFT JOIN clinicians k ON k.id = c.ordering_clinician_id
WHERE c.resulted_at + to_hours(c.rule_hours) <= {now}
ORDER BY started_at, c.id

Tests: count_between, unique, monday_morning