# NCR Pareto and Repeat Offender SQL Generator

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

## Short answer

Fill in your NCR table and column names to get ready-to-run SQL for a defect Pareto with cumulative percent and a repeat offender list.

## Category

developer

## Audience

- Quality engineer
- ERP analyst
- BI developer

## Tool

**Kind:** template

**Output:** code

**Language:** sql

**Fields:**

#### NCR table name

**Key:** ncr_table

**Type:** text

**Default:** quality_ncr

**Help:** Your actual table or view name

#### Defect code column

**Key:** defect_col

**Type:** text

**Default:** defect_code

#### Supplier column

**Key:** supplier_col

**Type:** text

**Default:** supplier_id

#### Part number column

**Key:** part_col

**Type:** text

**Default:** part_number

#### Rejected quantity column

**Key:** qty_col

**Type:** text

**Default:** reject_qty

#### NCR date column

**Key:** date_col

**Type:** text

**Default:** ncr_date

#### From date (YYYY-MM-DD)

**Key:** from_date

**Type:** date

**Default:** 2026-01-01

#### Minimum NCRs to count as repeat

**Key:** min_count

**Type:** number

**Default:** 3

**Blocks:**

#### 1. Defect Pareto with cumulative percent

**Body:** SELECT
  {{defect_col}} AS defect,
  COUNT(*) AS ncr_count,
  SUM({{qty_col}}) AS rejected_qty,
  ROUND(100.0 * SUM(COUNT(*)) OVER (ORDER BY COUNT(*) DESC, {{defect_col}}) / SUM(COUNT(*)) OVER (), 1) AS cumulative_pct
FROM {{ncr_table}}
WHERE {{date_col}} >= DATE '{{from_date}}'
GROUP BY {{defect_col}}
ORDER BY ncr_count DESC, {{defect_col}};

#### 2. Repeat offender suppliers

**Body:** SELECT
  {{supplier_col}} AS supplier,
  COUNT(*) AS ncr_count,
  COUNT(DISTINCT {{part_col}}) AS distinct_parts,
  SUM({{qty_col}}) AS rejected_qty
FROM {{ncr_table}}
WHERE {{date_col}} >= DATE '{{from_date}}'
GROUP BY {{supplier_col}}
HAVING COUNT(*) >= {{min_count}}
ORDER BY ncr_count DESC;

#### 3. Repeat offender parts with defect mix

**Body:** SELECT
  {{part_col}} AS part,
  {{defect_col}} AS defect,
  COUNT(*) AS ncr_count
FROM {{ncr_table}}
WHERE {{date_col}} >= DATE '{{from_date}}'
GROUP BY {{part_col}}, {{defect_col}}
HAVING COUNT(*) >= {{min_count}}
ORDER BY ncr_count DESC, {{part_col}};

**Notes:** Table and column names here are generic placeholders, not a specific ERP schema. Replace them with your own. Date literal syntax differs by database: DATE 'YYYY-MM-DD' works in PostgreSQL, Oracle and MySQL 8, while SQL Server prefers a plain 'YYYY-MM-DD' string or CAST. Test on a copy and review the query plan before running on a production system.

## Content

**Intro:** A Pareto of nonconformances tells you where to focus, and a repeat offender list shows which suppliers and parts keep coming back. This generator writes the SQL from your table and column names.

**Sections:**

#### How to use the generator

**Body:** Type the name of the table or view that holds your NCR records and the columns for defect code, supplier, part, rejected quantity and date. Choose a start date and a threshold for what counts as repeat. Copy the three queries into your SQL client, BI tool or ERP reporting tool. If your NCRs are spread over a header and a line table, create a view that joins them and point the generator at the view.

#### How the Pareto query works

**Body:** The first query counts NCRs per defect code, then uses a window function to produce a running total divided by the grand total. Sorting by count descending means the cumulative percent shows how few defect types account for most of the problem. The usual reading is to start with the types that cover roughly the first 70 to 80 percent. The ORDER BY inside the window includes the defect code so ties produce stable results.

**Bullets:**

- Count NCRs, not just quantities, to avoid one large lot dominating
- Use rejected quantity to see impact on cost
- Group similar defect codes before drawing conclusions

#### Data quality cautions

**Body:** Pareto charts are only as good as the coding. If half your NCRs are coded Other, or inspectors use different codes for the same defect, the result misleads. Check the top codes against a sample of descriptions before acting. Also decide whether to count NCRs by date raised or date closed, and be consistent. For supplier decisions, compare against delivered quantity so a high-volume supplier is not unfairly flagged.

**Cta:** If your NCR data sits in an ERP with awkward schemas, we can help build a clean read-only quality view that these queries can run on.

**Faq:**

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

**Answer:** The window function syntax works on PostgreSQL, SQL Server 2012 and later, Oracle and MySQL 8. The DATE literal differs slightly, so adjust it if your database rejects it. See the notes under the generator.

#### Can I use this on SAP or Epicor tables?

**Answer:** Only after mapping the placeholders to real table and column names, or building a view. We do not assume a vendor schema here. Check your ERP data dictionary and run queries on a copy or reporting database.

#### What counts as a repeat offender?

**Answer:** That is your threshold. Three NCRs in the period is a common starting point, but low-volume suppliers may need a lower number, and you should normalise by delivered quantity before escalating.

#### How do I turn the result into a chart?

**Answer:** Paste the output into Excel and create a bar chart of ncr_count with cumulative_pct as a line on a secondary axis. That gives the classic Pareto chart.

## Search terms

ncr pareto sql, nonconformance report query, defect pareto sql query, repeat offender supplier query, ncr report sql server, cumulative percent sql window function, quality data sql template, nonconformance trend query

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