# Lean KPI SQL Query Generator for Downtime Pareto and Availability

> Source: https://netray.co/tools/lean-kpi-sql-query-generator
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: Free Tools

## Short answer

Fill in your own table and column names to get a downtime Pareto query and an availability query you can run against an MES or ERP reporting database.

## Category

developer

## Audience

- MES administrator
- ERP developer
- Lean practitioner

## Tool

**Kind:** template

**Output:** code

**Language:** sql

**Fields:**

#### Downtime event table

**Key:** downtime_table

**Type:** text

**Default:** mes_downtime_event

**Help:** Your table holding one row per stop.

#### Work center column

**Key:** work_center_col

**Type:** text

**Default:** work_center

#### Reason code column

**Key:** reason_col

**Type:** text

**Default:** reason_code

#### Start time column

**Key:** start_col

**Type:** text

**Default:** start_time

#### Duration in minutes expression

**Key:** minutes_expr

**Type:** text

**Default:** DATEDIFF(minute, start_time, end_time)

**Help:** SQL Server style by default. Use your database's date difference function.

#### From date

**Key:** from_date

**Type:** date

**Default:** 2026-10-01

#### To date (exclusive)

**Key:** to_date

**Type:** date

**Default:** 2026-11-01

#### Planned production minutes per work center for the period

**Key:** planned_minutes

**Type:** number

**Default:** 20160

**Help:** Example: 14 days x 24 hours x 60 minutes.

#### Rows to return

**Key:** top_n

**Type:** number

**Default:** 10

**Blocks:**

#### 1. Downtime Pareto by reason code

**Body:** -- Downtime minutes ranked by reason, with cumulative share
WITH d AS (
  SELECT {{reason_col}} AS reason,
         SUM({{minutes_expr}}) AS minutes
  FROM {{downtime_table}}
  WHERE {{start_col}} >= '{{from_date}}'
    AND {{start_col}} <  '{{to_date}}'
  GROUP BY {{reason_col}}
)
SELECT reason,
       minutes,
       ROUND(100.0 * minutes / SUM(minutes) OVER (), 1) AS pct_of_total,
       ROUND(100.0 * SUM(minutes) OVER (ORDER BY minutes DESC ROWS UNBOUNDED PRECEDING)
             / SUM(minutes) OVER (), 1) AS cumulative_pct
FROM d
ORDER BY minutes DESC;

#### 2. Availability by work center

**Body:** -- Availability = (planned minutes - downtime minutes) / planned minutes
SELECT {{work_center_col}} AS work_center,
       SUM({{minutes_expr}}) AS downtime_minutes,
       {{planned_minutes}} AS planned_minutes,
       ROUND(100.0 * ({{planned_minutes}} - SUM({{minutes_expr}})) / {{planned_minutes}}, 1) AS availability_pct
FROM {{downtime_table}}
WHERE {{start_col}} >= '{{from_date}}'
  AND {{start_col}} <  '{{to_date}}'
GROUP BY {{work_center_col}}
ORDER BY availability_pct ASC;

#### 3. Top work center and reason pairs

**Body:** -- Where does the largest loss sit?
SELECT {{work_center_col}} AS work_center,
       {{reason_col}} AS reason,
       COUNT(*) AS stops,
       SUM({{minutes_expr}}) AS minutes
FROM {{downtime_table}}
WHERE {{start_col}} >= '{{from_date}}'
  AND {{start_col}} <  '{{to_date}}'
GROUP BY {{work_center_col}}, {{reason_col}}
ORDER BY minutes DESC
LIMIT {{top_n}};

**Notes:** Table and column names here are generic placeholders, not a specific ERP schema. Replace them with your own. LIMIT is MySQL, PostgreSQL and SQLite syntax; on SQL Server use SELECT TOP and on Oracle use FETCH FIRST. The duration expression must match your database. Open stops with no end time need handling. Test on a copy, read only, before using the numbers.

## Content

**Intro:** Every OEE improvement starts with a Pareto of lost minutes. This generator writes three SQL queries from your own table and column names: a reason code Pareto, availability by work center, and the top work center and reason pairs. Copy them into your reporting tool.

**Sections:**

#### How to use the generator

**Body:** Enter the name of the table that holds one row per stop, then the columns for work center, reason and start time. Set the duration expression to match your database. Choose the date range with an exclusive end date and the planned production minutes for that period. Copy the generated SQL into a read only connection to a reporting replica or data warehouse, never into a live transactional database during peak load.

#### What the queries calculate

**Body:** The Pareto query sums downtime minutes by reason, then calculates each reason's share of the total and a running cumulative share, so you can see which few reasons cause most of the loss. The availability query uses the standard formula: planned minutes minus downtime minutes, divided by planned minutes. The third query shows the work center and reason pairs with the highest minutes, which tells you where to send the first improvement team.

**Bullets:**

- Pareto: ranks reasons and shows cumulative percent.
- Availability: planned minutes less downtime, over planned minutes.
- Pairs: pinpoints machine and cause together.

#### Data quality checks before trusting results

**Body:** Check that every stop has an end time, that overlapping events on the same work center are not double counted, and that planned downtime such as breaks is excluded or coded separately. Compare the total to shift calendars. If one reason such as Other dominates, the codes need improvement before the Pareto is useful. Run the same query for two periods to see whether the shape is stable.

#### Common mistakes

**Body:** Do not use a period where the planned minutes differ by work center without adjusting; the single planned minutes field in this generator assumes the same schedule. Avoid mixing time zones between the event table and your date filter. Also avoid acting on a few days of data, because short stops and rare breakdowns need weeks to show a stable pattern.

**Cta:** If you would rather ask for these numbers in plain language against your ERP or MES, ERPray can show the generated query and stay read-only on your own servers.

**Faq:**

#### Does this work with my ERP or MES schema?

**Answer:** It uses generic table and column names that you replace with your own, so it fits any relational schema that records one row per stop with a reason, work center, start and duration. It does not assume a specific vendor schema.

#### How is availability calculated?

**Answer:** Availability equals run time divided by planned production time. Run time is planned time minus downtime. The query computes it as planned minutes minus downtime minutes, divided by planned minutes, and shows the result as a percent.

#### Why does the Pareto show a cumulative percent?

**Answer:** The cumulative percent shows how many reasons it takes to reach a given share of total loss, often about 80 percent from a few reasons. It helps you choose where to focus instead of treating every reason equally.

#### Can I use it for scrap instead of downtime?

**Answer:** Yes. Point the table at a scrap or reject table, the reason column at the scrap reason, and replace the minutes expression with a quantity or cost column. The same Pareto logic applies to any ranked loss.

## Search terms

downtime pareto sql, oee sql query, mes downtime report sql, availability calculation sql, sql pareto analysis downtime reason, oee downtime query template, lean kpi sql, machine downtime sql report

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