# MRO Open Work Order Aging SQL Query Generator

> Source: https://netray.co/tools/mro-open-work-order-aging-sql-generator
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: Free Tools

## Short answer

Builds a SQL query that groups open maintenance work orders into aging buckets so planners can see what is stuck.

## Category

developer

## Industry

Aerospace MRO

## Audience

- ERP developer
- MRO planner
- Report writer

## Tool

**Kind:** template

**Output:** code

**Language:** sql

**Fields:**

#### Work order table

**Key:** wo_table

**Type:** text

**Default:** work_orders

**Help:** Replace with your actual table or view name.

#### Work order number column

**Key:** id_col

**Type:** text

**Default:** wo_number

#### Status column

**Key:** status_col

**Type:** text

**Default:** status

#### Open date column

**Key:** open_col

**Type:** text

**Default:** open_date

#### Station or shop column

**Key:** station_col

**Type:** text

**Default:** station

#### Closed status values

**Key:** closed_status

**Type:** text

**Default:** 'CLOSED','CANCELLED'

**Help:** Quoted and comma separated.

#### Bucket 1 upper limit (days)

**Key:** bucket1

**Type:** number

**Default:** 7

#### Bucket 2 upper limit (days)

**Key:** bucket2

**Type:** number

**Default:** 30

#### Bucket 3 upper limit (days)

**Key:** bucket3

**Type:** number

**Default:** 90

**Blocks:**

#### Aging summary by station (SQL Server style)

**Body:** SELECT
  {{station_col}},
  COUNT(*) AS open_work_orders,
  SUM(CASE WHEN DATEDIFF(day, {{open_col}}, GETDATE()) <= {{bucket1}} THEN 1 ELSE 0 END) AS age_0_to_{{bucket1}},
  SUM(CASE WHEN DATEDIFF(day, {{open_col}}, GETDATE()) > {{bucket1}} AND DATEDIFF(day, {{open_col}}, GETDATE()) <= {{bucket2}} THEN 1 ELSE 0 END) AS age_{{bucket1}}_to_{{bucket2}},
  SUM(CASE WHEN DATEDIFF(day, {{open_col}}, GETDATE()) > {{bucket2}} AND DATEDIFF(day, {{open_col}}, GETDATE()) <= {{bucket3}} THEN 1 ELSE 0 END) AS age_{{bucket2}}_to_{{bucket3}},
  SUM(CASE WHEN DATEDIFF(day, {{open_col}}, GETDATE()) > {{bucket3}} THEN 1 ELSE 0 END) AS age_over_{{bucket3}}
FROM {{wo_table}}
WHERE {{status_col}} NOT IN ({{closed_status}})
GROUP BY {{station_col}}
ORDER BY open_work_orders DESC;

#### Oldest open work orders (detail list)

**Body:** SELECT TOP 50
  {{id_col}},
  {{station_col}},
  {{status_col}},
  {{open_col}},
  DATEDIFF(day, {{open_col}}, GETDATE()) AS age_days
FROM {{wo_table}}
WHERE {{status_col}} NOT IN ({{closed_status}})
ORDER BY age_days DESC;

#### Same detail list for PostgreSQL or Oracle

**Body:** -- PostgreSQL
SELECT {{id_col}}, {{station_col}}, {{status_col}}, {{open_col}},
       (CURRENT_DATE - {{open_col}}::date) AS age_days
FROM {{wo_table}}
WHERE {{status_col}} NOT IN ({{closed_status}})
ORDER BY age_days DESC
LIMIT 50;

-- Oracle
SELECT {{id_col}}, {{station_col}}, {{status_col}}, {{open_col}},
       TRUNC(SYSDATE - {{open_col}}) AS age_days
FROM {{wo_table}}
WHERE {{status_col}} NOT IN ({{closed_status}})
ORDER BY age_days DESC
FETCH FIRST 50 ROWS ONLY;

**Notes:** Table and column names are placeholders. Map them to your maintenance system or ERP schema, and run against a read replica or reporting view, not the live transaction database. Confirm which status values mean closed in your system. Test on a small date range first.

## Content

**Intro:** Open work orders that sit untouched are a quiet cost in a repair station: parts wait, customers chase and turn times stretch. This generator writes a SQL aging query that you can point at your own work order table.

**Sections:**

#### How to use the generator

**Body:** Enter the names of your work order table and the columns that hold the order number, status, open date and station. Set the closed status values and the three bucket limits, then copy the query into your SQL client or reporting tool. The first block gives a summary by station, the second lists the oldest open orders, and the third shows PostgreSQL and Oracle versions of the date arithmetic.

**Bullets:**

- Replace every placeholder with a real name before running
- Use a reporting view where one exists
- Add a date range filter for large tables

#### Reading the output

**Body:** The summary shows for each station how many orders are in each age bucket. A healthy shop has most orders in the youngest buckets. A fat tail in the over 90 day bucket usually means orders waiting on parts, customer approval or a missed closing step rather than active work. Check the detail list to separate those causes, then compare the same station month by month to see whether the tail is growing.

#### Common mistakes

**Body:** The most frequent errors are counting cancelled orders as open, aging from the wrong date (creation date rather than induction date) and mixing work orders for different record types, such as quotes, in the same table. Also be careful with time zones when the open date is a timestamp. If your system keeps a status history table, age the order from the date it entered the current status to see how long it has been stuck, not only how old it is.

**Bullets:**

- Exclude cancelled and closed statuses
- Choose the date that matches your turn time definition
- Check for test or training orders

**Cta:** If planners ask these questions every week, we can set up a read-only assistant over your maintenance and ERP data that writes and shows the query it ran.

**Faq:**

#### Will this query work on my system as is?

**Answer:** No. Table and column names are placeholders and must be changed to your schema. The logic is standard SQL with date functions for SQL Server, PostgreSQL and Oracle, so only names and date syntax usually need adjusting.

#### Can I run it against the live ERP database?

**Answer:** Prefer a reporting replica or view. Aggregate queries over large work order tables can slow down transactions, and some vendors do not support direct queries against production tables.

#### What bucket sizes should I use?

**Answer:** Use limits that match your commitments. Many shops pick 7, 30 and 90 days. If your standard turn time is 21 days, set bucket two to 21 so the report shows what has gone past the promise.

#### How do I age by status date instead?

**Answer:** Join the status history table and take the latest date for the current status, then use that date in place of the open date column. Column names depend on your system.

## Search terms

open work order aging report sql, work order aging query, mro work order backlog sql, maintenance work order age buckets, mro reporting sql, aviation work order report, sql aging buckets datediff

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