Developer ToolsFree generatorPDF, Excel, Word export

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

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.

Fill in

FMEA line table name

The table holding one row per failure chain.

RPN threshold

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

Severity threshold

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

As-of date for overdue actions

Generated code

Download:

Open high-risk lines (RPN or high severity)

SELECT fmea_id,
       process_step,
       failure_mode,
       severity,
       occurrence,
       detection,
       severity * occurrence * detection AS rpn,
       action_status
FROM   fmea_line
WHERE  (severity * occurrence * detection >= 100
        OR severity >= 9)
  AND  action_status <> 'Closed'
ORDER BY severity DESC,
         severity * occurrence * detection DESC;

Overdue recommended actions

SELECT fmea_id,
       failure_mode,
       action_owner,
       target_date,
       action_status
FROM   fmea_line
WHERE  action_status <> 'Closed'
  AND  target_date < CAST('2026-10-20' AS DATE)
ORDER BY target_date;

Risk roll-up by process step

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

Cumulative Pareto of RPN by failure mode

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   fmea_line
       GROUP BY failure_mode) t
ORDER BY rpn DESC;

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.

Ask your quality data in plain language

Optional: see how a read-only natural-language layer could answer FMEA and NCR questions from your own data.

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

How to use the queries

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

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

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.

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

Frequently Asked Questions

Does this match my QMS or ERP schema?

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?

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?

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?

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.

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.

Talk to an Expert Explore More Free Tools

Related Tools

Quality & Inspection

More Developer Tools tools

See all

Back to All Tools