Machines Due for Preventive Maintenance by Quarter End
Every machine needs preventive maintenance (PM) every pm_interval_days days after its last preventive service.
- Difficulty: Medium
- Topics: Subqueries, Dates
- Dialect: MySQL
- Problem: #260
Problem statement
Every machine needs preventive maintenance (PM) every pm_interval_days days after its last preventive service. Breakdown repairs are logged too, but they do not reset the PM clock.
List every machine whose next PM is due on or before 2025-03-31, plus every machine that has never had a preventive service. Return machine_name, last_pm (the date of its latest preventive service, NULL if none) and next_due (last_pm plus the interval, NULL if none). Order by machine_name.
Tables
Table: PlantMachine
| Column | Type |
|---|---|
| machine_id | int |
| machine_name | varchar |
| pm_interval_days | int |
Primary key: machine_id.
Machine names are unique.
Table: MaintenanceLog
| Column | Type |
|---|---|
| log_id | int |
| machine_id | int |
| done_on | date |
| kind | enum(preventive, breakdown) |
Primary key: log_id.
Examples
Example 1
PlantMachine
| machine_id | machine_name | pm_interval_days |
|---|---|---|
| 1 | Compressor-1 | 90 |
| 2 | CNC-07 | 180 |
| 3 | Boiler-2 | 60 |
| 4 | Press-12 | 120 |
| 5 | Chiller-3 | 90 |
MaintenanceLog
| log_id | machine_id | done_on | kind |
|---|---|---|---|
| 1 | 1 | 2024-10-10 | preventive |
| 2 | 1 | 2024-12-20 | preventive |
| 3 | 2 | 2024-08-01 | preventive |
| 4 | 3 | 2025-02-25 | breakdown |
| 5 | 4 | 2024-12-01 | preventive |
| 6 | 5 | 2025-01-01 | preventive |
| 7 | 2 | 2025-01-15 | breakdown |
Output
| machine_name | last_pm | next_due |
|---|---|---|
| Boiler-2 | NULL | NULL |
| CNC-07 | 2024-08-01 | 2025-01-28 |
| Compressor-1 | 2024-12-20 | 2025-03-20 |
| Press-12 | 2024-12-01 | 2025-03-31 |
How to solve Machines Due for Preventive Maintenance by Quarter End
The latest PM per machine is MAX(done_on) over the preventive rows only — filtering kind = 'preventive' inside the aggregation is what stops a recent breakdown repair from resetting the clock. That derived table has a row only for machines that were ever serviced, so it is LEFT JOINed to PlantMachine; machines never serviced come through with last_pm NULL.
The next due date is DATE_ADD(last_pm, INTERVAL pm_interval_days DAY), and the machine is listed when that is <= '2025-03-31' or when there is no PM at all. Since the due date of an unserviced machine is NULL, it needs the explicit IS NULL branch, and selecting the same expression shows NULL for it.
A correlated scalar subquery can fetch the latest PM per machine instead, with the due test written as DATEDIFF('2025-03-31', last_pm) >= pm_interval_days. With an index on (machine_id, kind, done_on) both read one index entry per machine.
Reference solution (MySQL)
SELECT m.machine_name, p.last_pm,
DATE_ADD(p.last_pm, INTERVAL m.pm_interval_days DAY) AS next_due
FROM PlantMachine m
LEFT JOIN (
SELECT machine_id, MAX(done_on) AS last_pm
FROM MaintenanceLog
WHERE kind = 'preventive'
GROUP BY machine_id
) p ON p.machine_id = m.machine_id
WHERE p.last_pm IS NULL
OR DATE_ADD(p.last_pm, INTERVAL m.pm_interval_days DAY) <= '2025-03-31'
ORDER BY m.machine_nameAnother way
SELECT machine_name, last_pm, DATE_ADD(last_pm, INTERVAL pm_interval_days DAY) AS next_due FROM (
SELECT m.machine_name, m.pm_interval_days,
(SELECT MAX(l.done_on) FROM MaintenanceLog l WHERE l.machine_id = m.machine_id AND l.kind = 'preventive') AS last_pm
FROM PlantMachine m
) t
WHERE last_pm IS NULL OR DATEDIFF('2025-03-31', last_pm) >= pm_interval_days
ORDER BY machine_name← Late Work Order Rate by Line · Batches Worse Than Their Product's Defect Rate →