OEE SQL Query Generator

WITH counts AS (
  SELECT line_id AS line_id,
         production_date AS d,
         SUM(total_count) AS total_count,
         SUM(good_count) AS good_count
  FROM production_counts
  GROUP BY line_id, production_date
),
stops AS (
  SELECT line_id AS line_id,
         stop_date AS d,
         SUM(stop_minutes) AS stop_minutes
  FROM stop_events
  GROUP BY line_id, stop_date
)
SELECT c.line_id,
       c.d,
       (450 - COALESCE(s.stop_minutes, 0)) / 450.0 AS availability,
       CASE WHEN (450 - COALESCE(s.stop_minutes, 0)) > 0
            THEN (0.5 * c.total_count) / (450 - COALESCE(s.stop_minutes, 0))
            ELSE 0 END AS performance,
       CASE WHEN c.total_count > 0
            THEN c.good_count * 1.0 / c.total_count
            ELSE 0 END AS quality,
       (c.good_count * 0.5) / 450.0 AS oee
FROM counts c
LEFT JOIN stops s ON s.line_id = c.line_id AND s.d = c.d
ORDER BY c.d, c.line_id;

-- Wrap the query above as a view named v_oee_daily, then:
SELECT *
FROM v_oee_daily
WHERE oee > 1.0
   OR performance > 1.0
   OR quality > 1.0;