# FMEA Risk Ranking SQL Query Generator: RPN, High Severity and Overdue Actions

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

## Short answer

Generates ANSI-style SQL for an FMEA table: open high-risk lines, overdue actions, risk roll-up by process step and a cumulative Pareto of RPN.

## Category

developer

## Audience

- Quality engineer
- ERP admin
- Data analyst

## Tool

**Kind:** template

**Output:** code

**Language:** sql

**Fields:**

#### FMEA line table name

**Key:** table

**Type:** text

**Default:** fmea_line

**Help:** The table holding one row per failure chain.

#### RPN threshold

**Key:** rpn_threshold

**Type:** number

**Default:** 100

**Help:** Lines at or above this S x O x D are listed.

#### Severity threshold

**Key:** sev_threshold

**Type:** number

**Default:** 9

**Help:** Lines at or above this severity are listed regardless of RPN.

#### As-of date for overdue actions

**Key:** as_of

**Type:** date

**Default:** 2026-10-20

**Blocks:**

#### Open high-risk lines (RPN or high severity)

**Body:** SELECT fmea_id,
       process_step,
       failure_mode,
       severity,
       occurrence,
       detection,
       severity * occurrence * detection AS rpn,
       action_status
FROM   {{table}}
WHERE  (severity * occurrence * detection >= {{rpn_threshold}}
        OR severity >= {{sev_threshold}})
  AND  action_status <> 'Closed'
ORDER BY severity DESC,
         severity * occurrence * detection DESC;

#### Overdue recommended actions

**Body:** SELECT fmea_id,
       failure_mode,
       action_owner,
       target_date,
       action_status
FROM   {{table}}
WHERE  action_status <> 'Closed'
  AND  target_date < CAST('{{as_of}}' AS DATE)
ORDER BY target_date;

#### Risk roll-up by process step

**Body:** SELECT process_step,
       COUNT(*) AS lines,
       MAX(severity * occurrence * detection) AS max_rpn,
       AVG(severity * occurrence * detection) AS avg_rpn,
       SUM(CASE WHEN severity >= {{sev_threshold}} THEN 1 ELSE 0 END) AS high_severity_lines
FROM   {{table}}
GROUP BY process_step
ORDER BY max_rpn DESC;

#### Cumulative Pareto of RPN by failure mode

**Body:** SELECT failure_mode,
       rpn,
       SUM(rpn) OVER (ORDER BY rpn DESC, failure_mode) AS cumulative_rpn,
       SUM(rpn) OVER () AS total_rpn
FROM  (SELECT failure_mode,
              SUM(severity * occurrence * detection) AS rpn
       FROM   {{table}}
       GROUP BY failure_mode) t
ORDER BY rpn DESC;

**Notes:** Column names (fmea_id, process_step, failure_mode, severity, occurrence, detection, action_status, action_owner, target_date) are generic placeholders for a typical FMEA table, not a specific ERP or QMS schema. Rename them to match your system. Window functions need SQL Server 2012 or later, PostgreSQL, Oracle, or MySQL 8. RPN is a legacy ranking; for AIAG-VDA analyses, rank by action priority and always review high severity lines first.

## Content

**Intro:** Once an FMEA lives in a database, a QMS or a spreadsheet loaded into one, the useful questions are repeatable: which open lines are riskiest, which actions are late, and where does risk concentrate. This generator writes the SQL for those questions against a generic FMEA line table.

**Sections:**

#### How to use the queries

**Body:** Enter the name of your FMEA line table, the RPN threshold, the severity threshold and the as-of date. Copy each generated query into your SQL client, then rename columns to match your schema. The first query lists open lines at or above the RPN threshold or the severity threshold, ordered by severity first. That ordering matters: a line with severity 9 or 10 deserves attention even when occurrence or detection are low. The second query lists overdue actions; the third rolls risk up by process step; the fourth builds a Pareto.

#### Why severity is sorted first

**Body:** RPN multiplies severity, occurrence and detection, which can hide critical items: severity 10 with occurrence 2 and detection 2 gives only 40, while a routine severity 4 line at 5 and 5 gives 100. The AIAG and VDA FMEA Handbook replaced RPN thresholds with an action priority table for that reason, rating action as High, Medium or Low from the S, O and D combination. These queries keep RPN because many sites still use it, but they sort and filter on severity as well.

#### Adapting the query to your system

**Body:** If your FMEA is split across header, structure and line tables, join them on the FMEA identifier before running the queries. If your tool stores ratings as text, cast them to integers first. For SQL Server, replace the CAST date literal if your regional settings cause format problems; the ISO yyyy-mm-dd form is safest. If you use a QMS with its own reporting layer, prefer its built-in reports and use SQL only for gaps.

**Bullets:**

- Join header and line tables on the FMEA identifier
- Cast text ratings to integers before multiplying
- Keep a revision or version column so you query only the current FMEA
- Do not query production QMS tables without read-only access

**Cta:** Netray can help expose FMEA, NCR and ERP quality data through read-only, auditable queries so quality teams can ask questions in plain language without writing SQL.

**Faq:**

#### Does this match my QMS or ERP schema?

**Answer:** No. The table and column names are generic placeholders for a typical FMEA line table. Rename them to match your system, and verify joins and revision handling against your own schema before using results for decisions.

#### Why not just rank by RPN?

**Answer:** RPN can hide high severity items because the product of three ratings treats very different combinations as equal. Many standards and customers now require action based on severity and on the AIAG-VDA action priority, so these queries filter on both.

#### Which databases does the SQL work on?

**Answer:** The statements use ANSI-style SQL with window functions, so they run on PostgreSQL, SQL Server 2012 and later, Oracle and MySQL 8, with only minor edits such as date literal handling.

#### Can I run this directly on my ERP database?

**Answer:** Be careful. Use a read-only account or a replica, test on a non-production copy and follow your change and audit rules. Prefer supported views or APIs where your ERP vendor provides them.

## Search terms

fmea database sql, fmea rpn sql query, fmea table schema, fmea overdue actions query, fmea pareto sql, quality management database queries, rpn calculation sql, fmea data model

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