Dimensional Inspection: Pass, Rework or Scrap — SQL Problem
The quality lab measures one critical dimension of every sampled part with a gauge, in microns.
- Difficulty: Easy
- Topics: Conditional Logic, Basics
- Dialect: MySQL
- Problem: #249
Problem statement
The quality lab measures one critical dimension of every sampled part with a gauge, in microns. A part's deviation is the absolute difference between measured_um and nominal_um.
Return check_id, part_no and verdict for every check, where verdict is 'PASS' when the deviation is at most tolerance_um, 'REWORK' when it is more than the tolerance but at most twice the tolerance, 'SCRAP' when it is more than twice the tolerance, and 'RECHECK' when the gauge reading is missing (measured_um is NULL). Order the rows by check_id.
Tables
Table: DimensionCheck
| Column | Type |
|---|---|
| check_id | int |
| part_no | varchar |
| nominal_um | int |
| measured_um | int |
| tolerance_um | int |
Primary key: check_id.
One gauge check of one sampled part. All sizes are in microns; measured_um is NULL when the gauge reading was not captured.
Examples
Example 1
DimensionCheck
| check_id | part_no | nominal_um | measured_um | tolerance_um |
|---|---|---|---|---|
| 1 | SHF-330 | 25000 | 25012 | 20 |
| 2 | SHF-330 | 25000 | 24980 | 20 |
| 3 | VLV-415 | 12500 | 12541 | 20 |
| 4 | VLV-415 | 12500 | 12540 | 20 |
| 5 | PIN-520 | 8000 | NULL | 10 |
| 6 | PIN-520 | 8000 | 7985 | 10 |
Output
| check_id | part_no | verdict |
|---|---|---|
| 1 | SHF-330 | PASS |
| 2 | SHF-330 | PASS |
| 3 | VLV-415 | SCRAP |
| 4 | VLV-415 | REWORK |
| 5 | PIN-520 | RECHECK |
| 6 | PIN-520 | REWORK |
How to solve Dimensional Inspection: Pass, Rework or Scrap
Every row gets a label, so this is a CASE expression in the SELECT list rather than a filter. The deviation is ABS(measured_um - nominal_um) — a part 12 microns under size is as far out as one 12 microns over.
CASE evaluates its WHEN branches in order and returns the first one that is true, so the bands can be written as plain upper bounds: at most the tolerance is a pass, otherwise at most twice the tolerance is rework, otherwise scrap. Using <= keeps a deviation exactly equal to a limit inside the better band, as the statement says.
The missing reading must be tested first. With measured_um NULL every comparison is unknown, no branch matches, and the ELSE would wrongly scrap the part. Putting measured_um IS NULL at the top gives it 'RECHECK'. Storing sizes as integer microns also keeps the comparisons exact — decimal millimetres held as floating point could put 10.05 − 10.00 a hair above 0.05. Nested IF calls with GREATEST in place of ABS give the same labels; either way it is one pass over the table.
Reference solution (MySQL)
SELECT check_id, part_no,
CASE
WHEN measured_um IS NULL THEN 'RECHECK'
WHEN ABS(measured_um - nominal_um) <= tolerance_um THEN 'PASS'
WHEN ABS(measured_um - nominal_um) <= 2 * tolerance_um THEN 'REWORK'
ELSE 'SCRAP'
END AS verdict
FROM DimensionCheck
ORDER BY check_idAnother way
SELECT check_id, part_no,
IF(measured_um IS NULL, 'RECHECK',
IF(GREATEST(measured_um - nominal_um, nominal_um - measured_um) <= tolerance_um, 'PASS',
IF(GREATEST(measured_um - nominal_um, nominal_um - measured_um) <= 2 * tolerance_um, 'REWORK', 'SCRAP'))) AS verdict
FROM DimensionCheck
ORDER BY check_id← Machines Overdue for Calibration on Audit Day · Sensors Silent Through January →