Machines Due for Preventive Maintenance by Quarter End

Medium
260.

Machines Due for Preventive Maintenance by Quarter End

MediumSubqueriesDates

MySQL

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

ColumnType
machine_idint
machine_namevarchar
pm_interval_daysint

Primary key: machine_id.

Machine names are unique.

Table: MaintenanceLog

ColumnTypeValues
log_idint
machine_idint
done_ondate
kindenumpreventive, breakdown

Primary key: log_id.

Example

The rows must come back in the order shown.

PlantMachine

machine_idmachine_namepm_interval_days
1Compressor-190
2CNC-07180
3Boiler-260
4Press-12120
5Chiller-390

MaintenanceLog

log_idmachine_iddone_onkind
112024-10-10preventive
212024-12-20preventive
322024-08-01preventive
432025-02-25breakdown
542024-12-01preventive
652025-01-01preventive
722025-01-15breakdown

Output

machine_namelast_pmnext_due
Boiler-2NULLNULL
CNC-072024-08-012025-01-28
Compressor-12024-12-202025-03-20
Press-122024-12-012025-03-31
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 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

ColumnType
machine_idint
machine_namevarchar
pm_interval_daysint

Primary key: machine_id.

Machine names are unique.

Table: MaintenanceLog

ColumnType
log_idint
machine_idint
done_ondate
kindenum(preventive, breakdown)

Primary key: log_id.

Examples

Example 1

PlantMachine

machine_idmachine_namepm_interval_days
1Compressor-190
2CNC-07180
3Boiler-260
4Press-12120
5Chiller-390

MaintenanceLog

log_idmachine_iddone_onkind
112024-10-10preventive
212024-12-20preventive
322024-08-01preventive
432025-02-25breakdown
542024-12-01preventive
652025-01-01preventive
722025-01-15breakdown

Output

machine_namelast_pmnext_due
Boiler-2NULLNULL
CNC-072024-08-012025-01-28
Compressor-12024-12-202025-03-20
Press-122024-12-012025-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_name

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