Out-of-Tolerance Dimensional Inspection SQL Query Generator

SELECT
    lot_no,
    characteristic_id,
    measured_value,
    lower_limit,
    upper_limit,
    CASE
        WHEN measured_value < lower_limit THEN measured_value - lower_limit
        ELSE measured_value - upper_limit
    END AS deviation_from_limit,
    inspected_at
FROM dbo.inspection_results
WHERE part_no = 'PN-48213'
  AND inspected_at >= '2026-07-01'
  AND inspected_at < '2026-09-30'
  AND (measured_value < lower_limit OR measured_value > upper_limit)
ORDER BY inspected_at DESC;

SELECT
    characteristic_id,
    COUNT(*) AS measurements,
    SUM(CASE WHEN measured_value < lower_limit OR measured_value > upper_limit THEN 1 ELSE 0 END) AS out_of_tolerance,
    100.0 * SUM(CASE WHEN measured_value < lower_limit OR measured_value > upper_limit THEN 1 ELSE 0 END) / COUNT(*) AS pct_out,
    AVG(measured_value) AS mean_value,
    MIN(measured_value) AS min_value,
    MAX(measured_value) AS max_value,
    STDEV(measured_value) AS std_dev,
    MIN(lower_limit) AS lsl,
    MIN(upper_limit) AS usl
FROM dbo.inspection_results
WHERE part_no = 'PN-48213'
  AND inspected_at >= '2026-07-01'
  AND inspected_at < '2026-09-30'
GROUP BY characteristic_id
ORDER BY pct_out DESC, characteristic_id;

SELECT characteristic_id, AVG(measured_value) AS mean_value, MIN(lower_limit) AS lsl, MIN(upper_limit) AS usl
FROM dbo.inspection_results
WHERE part_no = 'PN-48213'
  AND inspected_at >= '2026-07-01'
  AND inspected_at < '2026-09-30'
GROUP BY characteristic_id
HAVING AVG(measured_value) < MIN(lower_limit) + 0.25 * (MIN(upper_limit) - MIN(lower_limit))
    OR AVG(measured_value) > MIN(upper_limit) - 0.25 * (MIN(upper_limit) - MIN(lower_limit));