Machines Overdue for Calibration on Audit Day

Easy
248.

Machines Overdue for Calibration on Audit Day

EasyBasicsDates

MySQL

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

ColumnTypeValues
machine_idint
asset_tagvarchar
machine_typeenumCNC, Press, Lathe, Welder, Moulder
last_calibrateddate
calibration_interval_daysint

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.

Example

The rows must come back in the order shown.

Machine

machine_idasset_tagmachine_typelast_calibratedcalibration_interval_days
1PUN-CNC-0007CNC2024-06-10180
2PUN-PRS-0012Press2024-12-0190
3PUN-LTH-0003LatheNULL365
4PUN-WLD-0021Welder2024-10-1790
5PUN-CNC-0008CNC2024-07-19180
6PUN-MLD-0002Moulder2023-12-20365

Output

machine_idasset_tagdue_date
1PUN-CNC-00072024-12-07
3PUN-LTH-0003NULL
6PUN-MLD-00022024-12-19
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
machine_idint
asset_tagvarchar
machine_typeenum(CNC, Press, Lathe, Welder, Moulder)
last_calibrateddate
calibration_interval_daysint

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_idasset_tagmachine_typelast_calibratedcalibration_interval_days
1PUN-CNC-0007CNC2024-06-10180
2PUN-PRS-0012Press2024-12-0190
3PUN-LTH-0003LatheNULL365
4PUN-WLD-0021Welder2024-10-1790
5PUN-CNC-0008CNC2024-07-19180
6PUN-MLD-0002Moulder2023-12-20365

Output

machine_idasset_tagdue_date
1PUN-CNC-00072024-12-07
3PUN-LTH-0003NULL
6PUN-MLD-00022024-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_id

Another 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 →