Lean KPI SQL Query Generator for Downtime Pareto and Availability

-- Downtime minutes ranked by reason, with cumulative share
WITH d AS (
  SELECT reason_code AS reason,
         SUM(DATEDIFF(minute, start_time, end_time)) AS minutes
  FROM mes_downtime_event
  WHERE start_time >= '2026-10-01'
    AND start_time <  '2026-11-01'
  GROUP BY reason_code
)
SELECT reason,
       minutes,
       ROUND(100.0 * minutes / SUM(minutes) OVER (), 1) AS pct_of_total,
       ROUND(100.0 * SUM(minutes) OVER (ORDER BY minutes DESC ROWS UNBOUNDED PRECEDING)
             / SUM(minutes) OVER (), 1) AS cumulative_pct
FROM d
ORDER BY minutes DESC;

-- Availability = (planned minutes - downtime minutes) / planned minutes
SELECT work_center AS work_center,
       SUM(DATEDIFF(minute, start_time, end_time)) AS downtime_minutes,
       20160 AS planned_minutes,
       ROUND(100.0 * (20160 - SUM(DATEDIFF(minute, start_time, end_time))) / 20160, 1) AS availability_pct
FROM mes_downtime_event
WHERE start_time >= '2026-10-01'
  AND start_time <  '2026-11-01'
GROUP BY work_center
ORDER BY availability_pct ASC;

-- Where does the largest loss sit?
SELECT work_center AS work_center,
       reason_code AS reason,
       COUNT(*) AS stops,
       SUM(DATEDIFF(minute, start_time, end_time)) AS minutes
FROM mes_downtime_event
WHERE start_time >= '2026-10-01'
  AND start_time <  '2026-11-01'
GROUP BY work_center, reason_code
ORDER BY minutes DESC
LIMIT 10;