# Out-of-Tolerance Dimensional Inspection SQL Query Generator

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

## Short answer

Fill in your inspection results table and column names to get SQL that finds out-of-tolerance measurements and summarizes them by characteristic.

## Category

developer

## Industry

Discrete Manufacturing

## Audience

- Quality engineer
- ERP administrator
- Data analyst
- Manufacturing engineer

## Tool

**Kind:** template

**Output:** code

**Language:** sql

**Fields:**

#### Inspection results table

**Key:** table

**Type:** text

**Default:** dbo.inspection_results

#### Part number column

**Key:** part_col

**Type:** text

**Default:** part_no

#### Characteristic column

**Key:** char_col

**Type:** text

**Default:** characteristic_id

#### Lot or serial column

**Key:** lot_col

**Type:** text

**Default:** lot_no

#### Measured value column

**Key:** value_col

**Type:** text

**Default:** measured_value

#### Lower limit column

**Key:** lsl_col

**Type:** text

**Default:** lower_limit

#### Upper limit column

**Key:** usl_col

**Type:** text

**Default:** upper_limit

#### Inspection date column

**Key:** date_col

**Type:** text

**Default:** inspected_at

#### Part number to analyze

**Key:** part_no

**Type:** text

**Default:** PN-48213

#### From date

**Key:** from_date

**Type:** date

**Default:** 2026-07-01

#### To date

**Key:** to_date

**Type:** date

**Default:** 2026-09-30

**Blocks:**

#### 1. List every out-of-tolerance measurement

**Body:** SELECT
    {{lot_col}},
    {{char_col}},
    {{value_col}},
    {{lsl_col}},
    {{usl_col}},
    CASE
        WHEN {{value_col}} < {{lsl_col}} THEN {{value_col}} - {{lsl_col}}
        ELSE {{value_col}} - {{usl_col}}
    END AS deviation_from_limit,
    {{date_col}}
FROM {{table}}
WHERE {{part_col}} = '{{part_no}}'
  AND {{date_col}} >= '{{from_date}}'
  AND {{date_col}} < '{{to_date}}'
  AND ({{value_col}} < {{lsl_col}} OR {{value_col}} > {{usl_col}})
ORDER BY {{date_col}} DESC;

#### 2. Summary per characteristic

**Body:** SELECT
    {{char_col}},
    COUNT(*) AS measurements,
    SUM(CASE WHEN {{value_col}} < {{lsl_col}} OR {{value_col}} > {{usl_col}} THEN 1 ELSE 0 END) AS out_of_tolerance,
    100.0 * SUM(CASE WHEN {{value_col}} < {{lsl_col}} OR {{value_col}} > {{usl_col}} THEN 1 ELSE 0 END) / COUNT(*) AS pct_out,
    AVG({{value_col}}) AS mean_value,
    MIN({{value_col}}) AS min_value,
    MAX({{value_col}}) AS max_value,
    STDEV({{value_col}}) AS std_dev,
    MIN({{lsl_col}}) AS lsl,
    MIN({{usl_col}}) AS usl
FROM {{table}}
WHERE {{part_col}} = '{{part_no}}'
  AND {{date_col}} >= '{{from_date}}'
  AND {{date_col}} < '{{to_date}}'
GROUP BY {{char_col}}
ORDER BY pct_out DESC, {{char_col}};

#### 3. Characteristics close to a limit (mean within 25 percent of the band)

**Body:** SELECT {{char_col}}, AVG({{value_col}}) AS mean_value, MIN({{lsl_col}}) AS lsl, MIN({{usl_col}}) AS usl
FROM {{table}}
WHERE {{part_col}} = '{{part_no}}'
  AND {{date_col}} >= '{{from_date}}'
  AND {{date_col}} < '{{to_date}}'
GROUP BY {{char_col}}
HAVING AVG({{value_col}}) < MIN({{lsl_col}}) + 0.25 * (MIN({{usl_col}}) - MIN({{lsl_col}}))
    OR AVG({{value_col}}) > MIN({{usl_col}}) - 0.25 * (MIN({{usl_col}}) - MIN({{lsl_col}}));

**Notes:** Table and column names are generic placeholders, so map them to your own quality or ERP inspection tables. The script uses SQL Server syntax: use STDDEV or STDDEV_SAMP on Oracle, PostgreSQL and MySQL, and a bound parameter instead of an inline part number in production code. Limits that vary by lot should be grouped per limit pair. Test against a copy of the data before scheduling.

## Content

**Intro:** Most quality systems store inspection values along with lower and upper limits. These three queries turn that raw data into a rejection list, a per-characteristic summary and an early warning for characteristics drifting toward a limit.

**Sections:**

#### How to use the generator

**Body:** Enter the name of your inspection results table and map each column: part number, characteristic, lot, measured value, limits and date. Add the part number and the date range, then copy the queries into your SQL client or report tool. The first query lists every failing measurement with its distance from the nearest limit. The second summarizes each characteristic with count, percent out, mean, spread and limits. The third finds characteristics whose average sits close to a limit.

**Bullets:**

- Run query 2 first to find which characteristics deserve attention
- Use query 1 to pull the lots for containment or an NCR
- Schedule query 3 weekly as a drift check

#### Why the summary matters

**Body:** A percent-out figure only tells you that something failed. Comparing the mean and spread to the limits tells you why. A mean near one limit with a small standard deviation points to a centering problem, which is cheaper to fix with an offset. A mean at the center with a large standard deviation points to a variation problem, which needs a process fix. From the mean and the standard deviation you can also estimate Cpk as the smaller of (USL minus mean) and (mean minus LSL), divided by 3 sigma.

#### Reading the output

**Body:** Suppose a bore characteristic shows 400 measurements, 12 out of tolerance, a mean of 25.012 against limits of 24.990 and 25.020, and a standard deviation of 0.006. The mean is within 0.008 of the upper limit, and the rejects are all above it. Shifting the process mean down by about 0.008 would remove most of the failures. That is the kind of conclusion a raw failure list does not give you.

#### ERP and data notes

**Body:** Inspection data may live in an ERP quality module, a CMM database, an MES, or spreadsheets. Check how your system stores measurements: some store attribute results only, some store one row per sample with the limits in a separate plan table, requiring a join. Limits can change with drawing revision, so join to the revision if you need an exact historical picture. Confirm decimal precision and unit of measure before comparing values.

**Cta:** If inspection data sits in an ERP or MES that is hard to query, Netray can help set up a read-only, on-prem assistant that answers these questions and shows the SQL it ran.

**Faq:**

#### Will this work with SAP QM or SyteLine tables?

**Answer:** The table and column names here are generic placeholders. You need to map them to your own schema, and in many ERPs the limits live in a separate plan table that has to be joined. Check your vendor data model before running against production.

#### How do I get a Cpk from these results?

**Answer:** Take the mean and standard deviation from query 2, then compute the smaller of (USL minus mean) and (mean minus LSL), and divide by three times the standard deviation. Use it only if the data is from a stable process and is reasonably normal.

#### Why is the date filter written with less than the end date?

**Answer:** Using a half-open range avoids losing results from the last day when the column has a time part. If you want the end date included, set the To date to the following day.

#### Is it safe to run on a production database?

**Answer:** These are read-only SELECT queries, but a large unindexed table can still slow other users. Run them on a reporting copy or with a narrow date range, check the execution plan, and use parameters rather than pasted values in any scheduled job.

## Search terms

sql out of tolerance query, inspection results sql report, dimensional inspection data query, sql spc out of spec measurements, quality inspection sql template, find nonconforming measurements sql, inspection characteristic summary sql, cmm results sql

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