# Overdue PM Work Orders SQL Query Generator

> Source: https://netray.co/tools/cmms-overdue-pm-work-orders-sql-generator
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: Free Tools

## Short answer

Fill in your table and column names and get a ready SQL query for overdue PM work orders and a PM compliance summary, in SQL Server and PostgreSQL syntax.

## Category

maintenance

## Audience

- CMMS administrator
- Maintenance planner
- BI developer

## Tool

**Kind:** template

**Output:** code

**Language:** sql

**Fields:**

#### Work order table

**Key:** wo_table

**Type:** text

**Default:** work_orders

**Help:** Replace with your CMMS or ERP table name

#### Work order number column

**Key:** wo_no

**Type:** text

**Default:** wo_number

#### Asset or equipment column

**Key:** asset

**Type:** text

**Default:** asset_id

#### Due date column

**Key:** due

**Type:** text

**Default:** due_date

#### Completion date column

**Key:** done

**Type:** text

**Default:** completed_date

#### Work type column

**Key:** wo_type

**Type:** text

**Default:** work_type

#### Value that marks a PM work order

**Key:** pm_value

**Type:** text

**Default:** PM

#### Grace days before a PM counts as late

**Key:** grace

**Type:** text

**Default:** 0

**Blocks:**

#### SQL Server: open PM work orders that are overdue

**Body:** SELECT wo.{{wo_no}},
       wo.{{asset}},
       wo.{{due}},
       DATEDIFF(day, wo.{{due}}, CAST(GETDATE() AS date)) AS days_overdue
FROM {{wo_table}} wo
WHERE wo.{{wo_type}} = '{{pm_value}}'
  AND wo.{{done}} IS NULL
  AND wo.{{due}} < DATEADD(day, -{{grace}}, CAST(GETDATE() AS date))
ORDER BY days_overdue DESC;

#### PostgreSQL: open PM work orders that are overdue

**Body:** SELECT wo.{{wo_no}},
       wo.{{asset}},
       wo.{{due}},
       (CURRENT_DATE - wo.{{due}}::date) AS days_overdue
FROM {{wo_table}} wo
WHERE wo.{{wo_type}} = '{{pm_value}}'
  AND wo.{{done}} IS NULL
  AND wo.{{due}}::date < CURRENT_DATE - {{grace}}
ORDER BY days_overdue DESC;

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

**Body:** SELECT date_trunc('month', wo.{{due}}) AS due_month,
       COUNT(*) AS pms_due,
       COUNT(*) FILTER (WHERE wo.{{done}} IS NOT NULL AND wo.{{done}}::date <= wo.{{due}}::date + {{grace}}) AS pms_on_time,
       ROUND(100.0 * COUNT(*) FILTER (WHERE wo.{{done}} IS NOT NULL AND wo.{{done}}::date <= wo.{{due}}::date + {{grace}}) / NULLIF(COUNT(*), 0), 1) AS compliance_pct
FROM {{wo_table}} wo
WHERE wo.{{wo_type}} = '{{pm_value}}'
  AND wo.{{due}} < CURRENT_DATE
GROUP BY 1
ORDER BY 1;

**Notes:** 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.

## Content

**Intro:** 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.

**Sections:**

#### How to use the generator

**Body:** 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.

**Bullets:**

- 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

**Body:** 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

**Body:** 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

**Body:** 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.

**Cta:** 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.

**Faq:**

#### Will this work with my CMMS out of the box?

**Answer:** 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?

**Answer:** 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?

**Answer:** 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?

**Answer:** 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.

## Search terms

overdue pm work orders sql, cmms overdue work order query, pm compliance sql query, overdue maintenance report sql, maintenance backlog sql, work order aging sql, cmms sql report, preventive maintenance overdue report

---
Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json