# NCR Trend SQL Query Generator (Pareto by Supplier, Part, Cause)

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

## Short answer

Builds a SQL query for NCR counts and Pareto ranking by supplier, part or cause code over a date range using your own table and column names.

## Category

quality

## Industry

Aerospace

## Audience

- Quality engineer
- ERP developer
- Supplier quality

## Tool

**Kind:** template

**Output:** code

**Language:** sql

**Fields:**

#### NCR table name

**Key:** table

**Type:** text

**Default:** quality_ncr

**Help:** Your table name; generic by design

#### NCR date column

**Key:** date_col

**Type:** text

**Default:** ncr_date

#### Group by column

**Key:** group_col

**Type:** select

**Options:**

- supplier_id
- part_number
- cause_code
- defect_code

**Default:** supplier_id

#### Rejected quantity column

**Key:** qty_col

**Type:** text

**Default:** qty_rejected

#### Start date

**Key:** start

**Type:** date

**Default:** 2026-01-01

#### End date

**Key:** end

**Type:** date

**Default:** 2026-09-30

#### Top N rows

**Key:** top_n

**Type:** number

**Default:** 10

**Blocks:**

#### NCR Pareto query

**Body:** -- NCR Pareto by {{group_col}}, {{start}} to {{end}}
-- Table and column names are generic. Replace with your schema.
SELECT
  {{group_col}} AS category,
  COUNT(*) AS ncr_count,
  SUM({{qty_col}}) AS total_qty_rejected,
  ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 1) AS pct_of_total,
  ROUND(100.0 * SUM(COUNT(*)) OVER (ORDER BY COUNT(*) DESC ROWS UNBOUNDED PRECEDING) / SUM(COUNT(*)) OVER (), 1) AS cumulative_pct
FROM {{table}}
WHERE {{date_col}} >= '{{start}}'
  AND {{date_col}} <= '{{end}}'
GROUP BY {{group_col}}
ORDER BY ncr_count DESC
LIMIT {{top_n}};

#### Monthly trend variant

**Body:** -- Monthly NCR count trend
SELECT
  EXTRACT(YEAR FROM {{date_col}}) AS yr,
  EXTRACT(MONTH FROM {{date_col}}) AS mon,
  COUNT(*) AS ncr_count
FROM {{table}}
WHERE {{date_col}} >= '{{start}}'
  AND {{date_col}} <= '{{end}}'
GROUP BY EXTRACT(YEAR FROM {{date_col}}), EXTRACT(MONTH FROM {{date_col}})
ORDER BY yr, mon;

**Notes:** Syntax uses ANSI window functions with LIMIT, which works in PostgreSQL, MySQL 8 and similar. For SQL Server replace LIMIT with TOP, and for Oracle use FETCH FIRST. Table and column names are placeholders. Test on a copy and in read-only mode first.

## Content

**Intro:** A Pareto of nonconformances by supplier, part or cause tells you where corrective action effort pays back. This generator writes a portable SQL query for it, using your own table and column names.

**Sections:**

#### How to use the generator

**Body:** Enter the name of your NCR table and the columns holding the date, the grouping field and the rejected quantity. Choose the date window and how many rows to return, then copy the SQL. The first block ranks categories by NCR count with a percent of total and cumulative percent, the numbers needed to draw a Pareto chart. The second block gives a monthly trend you can paste into a spreadsheet or BI tool.

#### Reading a Pareto result

**Body:** Look at the cumulative percentage column. The few categories that reach about eighty percent of NCRs are your focus list. Rank by count first, then check quantity and cost, since a supplier with few NCRs but large scrap value may matter more. Trend the monthly block after a corrective action to see whether the count actually falls.

**Bullets:**

- Use consistent cause codes before trusting the chart
- Separate supplier, in-process and customer NCRs
- Review by value as well as count

#### Adapting to your ERP or QMS

**Body:** ERP schemas vary widely. Quality modules in SAP, Oracle, Epicor or SyteLine each use their own table and field names, so this tool uses generic placeholders rather than guessing. Ask your administrator for the NCR header and detail tables, and join to the vendor and item masters if you want names instead of codes.

#### Common mistakes

**Body:** Mixing closed and voided NCRs, counting an NCR once per line when you want one per record, and using free-text cause fields that cannot be grouped all distort the chart. Filter status deliberately and standardize cause codes.

**Cta:** If NCR reporting is stuck in spreadsheets, we can discuss read-only queries or an AI assistant over your quality data on-premises.

**Faq:**

#### Will this run on my database as is?

**Answer:** Probably not without edits. Table and column names are placeholders, and window function and limit syntax vary by database. Adjust names and the row limit clause for your platform.

#### Does it need write access?

**Answer:** No. It is a read-only SELECT. Run it with a read-only account against a reporting copy where possible.

#### How do I get a Pareto chart?

**Answer:** Paste the result into Excel and chart the count as columns and the cumulative percent as a line on a secondary axis.

#### Why generic names?

**Answer:** Quality tables differ by ERP and version. Using made-up vendor-specific names would be misleading, so you map your own schema.

## Search terms

NCR trend SQL query, nonconformance pareto SQL, supplier NCR report query, quality SQL report template, defect cause code SQL, NCR dashboard query

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