SQL Query Generator for X-bar and R Control Limits

WITH subgroup_stats AS (
  SELECT
    subgroup_id AS subgroup_id,
    COUNT(*) AS n,
    AVG(measured_value) AS xbar,
    MAX(measured_value) - MIN(measured_value) AS r
  FROM inspection_results
  WHERE characteristic_code = 'BORE_DIA' AND inspected_on >= '2026-09-01'
  GROUP BY subgroup_id
  HAVING COUNT(*) = 5
),
center_lines AS (
  SELECT
    AVG(xbar) AS xbarbar,
    AVG(r) AS rbar,
    COUNT(*) AS subgroup_count
  FROM subgroup_stats
)
SELECT
  s.subgroup_id,
  s.xbar,
  s.r,
  c.xbarbar + 0.577 * c.rbar AS ucl_xbar,
  c.xbarbar AS cl_xbar,
  c.xbarbar - 0.577 * c.rbar AS lcl_xbar,
  2.114 * c.rbar AS ucl_r,
  c.rbar AS cl_r,
  0 * c.rbar AS lcl_r,
  CASE
    WHEN s.xbar > c.xbarbar + 0.577 * c.rbar THEN 'XBAR ABOVE UCL'
    WHEN s.xbar < c.xbarbar - 0.577 * c.rbar THEN 'XBAR BELOW LCL'
    WHEN s.r > 2.114 * c.rbar THEN 'RANGE ABOVE UCL'
    ELSE 'IN CONTROL'
  END AS status
FROM subgroup_stats s
CROSS JOIN center_lines c
ORDER BY s.subgroup_id;

-- Add to center_lines when you also need Cpk: sigma within = rbar / d2
-- d2 for n: 2 = 1.128, 3 = 1.693, 4 = 2.059, 5 = 2.326, 6 = 2.534
-- Cpk = LEAST(usl - xbarbar, xbarbar - lsl) / (3 * rbar / d2)