Machines Overdue for Calibration on Audit Day — SQL Problem
A quality auditor visits the plant on 2025-01-15. Every machine must be recalibrated every calibration_interval_days days after its last calibration; its…
- Difficulty: Easy
- Topics: Basics, Dates
- Dialect: MySQL
- Problem: #248
Problem statement
A quality auditor visits the plant on 2025-01-15. Every machine must be recalibrated every calibration_interval_days days after its last calibration; its due date is last_calibrated plus that many days.
Return every machine that is overdue on the audit day: its due date is strictly before 2025-01-15, or it has never been calibrated (last_calibrated is NULL). A machine due on the audit day itself is not overdue. Return machine_id, asset_tag and due_date (NULL for a machine never calibrated), ordered by machine_id.
Tables
Table: Machine
| Column | Type |
|---|---|
| machine_id | int |
| asset_tag | varchar |
| machine_type | enum(CNC, Press, Lathe, Welder, Moulder) |
| last_calibrated | date |
| calibration_interval_days | int |
Primary key: machine_id.
One row per machine on the shop floor. last_calibrated is NULL for a machine that has never been calibrated since it was installed.
Examples
Example 1
Machine
| machine_id | asset_tag | machine_type | last_calibrated | calibration_interval_days |
|---|---|---|---|---|
| 1 | PUN-CNC-0007 | CNC | 2024-06-10 | 180 |
| 2 | PUN-PRS-0012 | Press | 2024-12-01 | 90 |
| 3 | PUN-LTH-0003 | Lathe | NULL | 365 |
| 4 | PUN-WLD-0021 | Welder | 2024-10-17 | 90 |
| 5 | PUN-CNC-0008 | CNC | 2024-07-19 | 180 |
| 6 | PUN-MLD-0002 | Moulder | 2023-12-20 | 365 |
Output
| machine_id | asset_tag | due_date |
|---|---|---|
| 1 | PUN-CNC-0007 | 2024-12-07 |
| 3 | PUN-LTH-0003 | NULL |
| 6 | PUN-MLD-0002 | 2024-12-19 |
How to solve Machines Overdue for Calibration on Audit Day
The due date of each machine is DATE_ADD(last_calibrated, INTERVAL calibration_interval_days DAY) — the interval can be a column, so each row gets its own date. A machine is overdue when that date is earlier than the audit day; because the dates are compared as dates, < '2025-01-15' keeps a machine due on the 14th and drops one due on the 15th, which is exactly the "strictly before" rule.
The trap is the machine that was never calibrated. Its last_calibrated is NULL, so its due date is NULL too, and NULL < '2025-01-15' is unknown — a WHERE clause drops it. The statement counts it as overdue, so it needs its own last_calibrated IS NULL branch. Selecting the same expression as due_date prints NULL for those rows without any extra work.
The alternative counts days instead: DATEDIFF('2025-01-15', last_calibrated) is the age of the calibration, overdue when it exceeds the interval, and COALESCE(…, 1) turns the NULL comparison into a yes. It is one pass over the table either way.
Reference solution (MySQL)
SELECT machine_id, asset_tag,
DATE_ADD(last_calibrated, INTERVAL calibration_interval_days DAY) AS due_date
FROM Machine
WHERE last_calibrated IS NULL
OR DATE_ADD(last_calibrated, INTERVAL calibration_interval_days DAY) < '2025-01-15'
ORDER BY machine_idAnother way
SELECT machine_id, asset_tag, DATE_ADD(last_calibrated, INTERVAL calibration_interval_days DAY) AS due_date
FROM Machine
WHERE COALESCE(DATEDIFF('2025-01-15', last_calibrated) > calibration_interval_days, 1) = 1
ORDER BY machine_id← Leave Ledger Entries That Took the Balance Negative · Dimensional Inspection: Pass, Rework or Scrap →