Maintenance & ReliabilityFree generatorPDF, Excel, Word export

Overdue PM Work Orders SQL Query Generator

Overdue preventive maintenance is the first number a maintenance manager asks for, and the report in the CMMS often does not quite match how the plant defines late. This generator gives you plain SQL you can adapt to your own schema, so you can list overdue PMs and measure compliance by month.

Fill in

Work order table

Replace with your CMMS or ERP table name

Work order number column

Asset or equipment column

Due date column

Completion date column

Work type column

Value that marks a PM work order

Grace days before a PM counts as late

Generated code

Free, optional. Reopen it on any device.

Download:

SQL Server: open PM work orders that are overdue

SELECT wo.wo_number,
       wo.asset_id,
       wo.due_date,
       DATEDIFF(day, wo.due_date, CAST(GETDATE() AS date)) AS days_overdue
FROM work_orders wo
WHERE wo.work_type = 'PM'
  AND wo.completed_date IS NULL
  AND wo.due_date < DATEADD(day, -0, CAST(GETDATE() AS date))
ORDER BY days_overdue DESC;

PostgreSQL: open PM work orders that are overdue

SELECT wo.wo_number,
       wo.asset_id,
       wo.due_date,
       (CURRENT_DATE - wo.due_date::date) AS days_overdue
FROM work_orders wo
WHERE wo.work_type = 'PM'
  AND wo.completed_date IS NULL
  AND wo.due_date::date < CURRENT_DATE - 0
ORDER BY days_overdue DESC;

PostgreSQL: PM compliance by month (completed on or before due date)

SELECT date_trunc('month', wo.due_date) AS due_month,
       COUNT(*) AS pms_due,
       COUNT(*) FILTER (WHERE wo.completed_date IS NOT NULL AND wo.completed_date::date <= wo.due_date::date + 0) AS pms_on_time,
       ROUND(100.0 * COUNT(*) FILTER (WHERE wo.completed_date IS NOT NULL AND wo.completed_date::date <= wo.due_date::date + 0) / NULLIF(COUNT(*), 0), 1) AS compliance_pct
FROM work_orders wo
WHERE wo.work_type = 'PM'
  AND wo.due_date < CURRENT_DATE
GROUP BY 1
ORDER BY 1;

Table and column names are generic placeholders: no particular CMMS schema is assumed. Map them to your system (for example, check whether a status column is better than a null completion date), test on a copy of the data, and run read-only. Some systems store due dates as numbers or in a separate scheduling table.

Maintenance reporting from your ERP

Optional: we can set up read-only maintenance dashboards over your ERP data on your own infrastructure.

Optional. The tool and every export work without this. No spam.

Was this tool useful?

How to use the generator

Enter the names of your work order table and the columns that hold the work order number, asset, due date, completion date and work type. Set the value that marks a PM (some systems use PM, others use a numeric code or a separate order type). Copy the version for your database. Run it against a reporting replica or a copy where possible, and review the first results against a report you already trust.

SQL Server uses DATEDIFF and GETDATE
PostgreSQL uses date subtraction and CURRENT_DATE
The grace parameter allows a few days of tolerance
Adjust the completion test if your system uses a status column

What the queries calculate

The first query returns open PM work orders whose due date is earlier than today minus the grace period, ordered from the most overdue. The compliance query counts PMs due in each month and how many were completed on or before the due date plus the grace period, then divides. This is the common definition of PM compliance. Many sites use a window such as plus or minus 10 percent of the interval, which needs the interval in the query.

Definitions matter more than SQL

Agree the definition before comparing sites. Does compliance count the scheduled date or the original date after a reschedule? Does a PM cancelled because the asset was decommissioned count as due? Is a work order released but not started overdue? Write the rules next to the query. If a planner can reschedule freely, compliance can look excellent while the real interval is stretching.

Common mistakes

Do not join the work order table to a multi-row table such as labour or materials without grouping: you will count the same PM several times. Watch time zones on timestamp columns, and make sure that cancelled and rejected orders are excluded. Finally, do not run heavy queries on the live transaction database during a shift.

Frequently Asked Questions

Will this work with my CMMS out of the box?

Not without mapping. The names are generic on purpose because every CMMS and ERP uses a different schema. Replace the table and column names, and check how your system stores completion and cancellation. Test against a known report before relying on it.

How is PM compliance usually defined?

The most common definition is PM work orders completed on or before the due date, divided by PM work orders that came due in the period. Some plants add a tolerance window. Whichever you use, write it down and apply it consistently across sites.

Can I use this for Oracle or MySQL?

The logic carries over, but the date functions differ. In Oracle you can subtract dates directly and use SYSDATE, and in MySQL you use DATEDIFF and CURDATE. Edit the date expressions and the FILTER clause, which is PostgreSQL syntax, to a CASE expression.

Why is my overdue count different from the CMMS dashboard?

Usually because the dashboard excludes certain statuses, uses a scheduled date instead of a due date, or applies a grace period. Compare the definitions first, then compare a small sample of work orders one by one.

If you would rather ask this question in plain language, ERPray can answer it from your ERP data read-only and show you the exact query it ran.

Talk to an Expert Explore More Free Tools