Dimensional Inspection: Pass, Rework or Scrap

Easy
249.

Dimensional Inspection: Pass, Rework or Scrap

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

ColumnType
check_idint
part_novarchar
nominal_umint
measured_umint
tolerance_umint

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.

Example

The rows must come back in the order shown.

DimensionCheck

check_idpart_nonominal_ummeasured_umtolerance_um
1SHF-330250002501220
2SHF-330250002498020
3VLV-415125001254120
4VLV-415125001254020
5PIN-5208000NULL10
6PIN-5208000798510

Output

check_idpart_noverdict
1SHF-330PASS
2SHF-330PASS
3VLV-415SCRAP
4VLV-415REWORK
5PIN-520RECHECK
6PIN-520REWORK
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
check_idint
part_novarchar
nominal_umint
measured_umint
tolerance_umint

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_idpart_nonominal_ummeasured_umtolerance_um
1SHF-330250002501220
2SHF-330250002498020
3VLV-415125001254120
4VLV-415125001254020
5PIN-5208000NULL10
6PIN-5208000798510

Output

check_idpart_noverdict
1SHF-330PASS
2SHF-330PASS
3VLV-415SCRAP
4VLV-415REWORK
5PIN-520RECHECK
6PIN-520REWORK

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_id

Another 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 →