Lab Reports Breaching the 24-Hour Turnaround — SQL Problem
The hospital lab promises every report within 24 hours of sample collection.
- Difficulty: Easy
- Topics: Basics, Dates
- Dialect: MySQL
- Problem: #198
Problem statement
The hospital lab promises every report within 24 hours of sample collection. The quality team wants the breaches: a report delivered more than 24 hours after collection (exactly 24 hours is on time), or a report that has not been delivered yet (reported_at is NULL).
Return order_id, test_name and collected_at of every breach, ordered by collected_at, then order_id.
Tables
Table: LabOrder
| Column | Type |
|---|---|
| order_id | int |
| patient_id | int |
| test_name | varchar |
| collected_at | datetime |
| reported_at | datetime |
Primary key: order_id.
One row per lab test ordered. reported_at is when the signed report reached the doctor, or NULL while it is pending.
Examples
Example 1
LabOrder
| order_id | patient_id | test_name | collected_at | reported_at |
|---|---|---|---|---|
| 5001 | 301 | CBC | 2025-03-03 08:15:00 | 2025-03-03 13:40:00 |
| 5002 | 302 | Blood Culture | 2025-03-03 09:00:00 | 2025-03-05 10:20:00 |
| 5003 | 303 | HbA1c | 2025-03-03 10:30:00 | 2025-03-04 10:30:00 |
| 5004 | 301 | Lipid Profile | 2025-03-03 11:05:00 | NULL |
| 5005 | 304 | Thyroid Panel | 2025-03-03 11:05:00 | 2025-03-04 11:05:01 |
| 5006 | 305 | KFT | 2025-03-04 07:45:00 | 2025-03-04 18:00:00 |
Output
| order_id | test_name | collected_at |
|---|---|---|
| 5002 | Blood Culture | 2025-03-03 09:00:00 |
| 5004 | Lipid Profile | 2025-03-03 11:05:00 |
| 5005 | Thyroid Panel | 2025-03-03 11:05:00 |
How to solve Lab Reports Breaching the 24-Hour Turnaround
The query is a filter with two branches joined by OR. The first, reported_at IS NULL, catches the reports still pending — a NULL in any comparison yields unknown, so without this branch the pending tests, which are the worst breaches of all, would silently disappear from the list.
The second branch measures the turnaround. TIMESTAMPDIFF(SECOND, collected_at, reported_at) > 86400 is exact: a report delivered exactly 24 hours later gives 86400 and is on time, one second more is a breach. Using TIMESTAMPDIFF(HOUR, …) would be wrong here because it truncates — 24 hours and 59 minutes is still 24 whole hours. Comparing reported_at with DATE_ADD(collected_at, INTERVAL 24 HOUR) is the same test written as a date comparison, and wrapping the difference in COALESCE with a value past the limit folds the NULL case into one condition.
The order is fixed by the statement, with order_id breaking ties between samples collected at the same moment. One pass over the table.
Reference solution (MySQL)
SELECT order_id, test_name, collected_at
FROM LabOrder
WHERE reported_at IS NULL
OR TIMESTAMPDIFF(SECOND, collected_at, reported_at) > 86400
ORDER BY collected_at, order_idAnother way
SELECT order_id, test_name, collected_at FROM LabOrder WHERE reported_at IS NULL OR reported_at > DATE_ADD(collected_at, INTERVAL 24 HOUR) ORDER BY collected_at, order_idAnother way
SELECT order_id, test_name, collected_at FROM LabOrder WHERE COALESCE(TIMESTAMPDIFF(SECOND, collected_at, reported_at), 86401) > 86400 ORDER BY collected_at, order_id← Total Recruits Down Each Player's Referral Tree · Controlled Drug Prescriptions With Their Prescriber →