OEE from shift logs: availability, performance and quality done right

One shift, by hand, before any SQL

Line L1, A-shift, 480 minutes on the clock with a 30-minute scheduled lunch and tea break. Planned production time is 450 minutes. The line stopped 45 minutes for a mould jam and 25 minutes for a changeover between two SKUs, so run time is 380 minutes.

It produced 2,400 caps of SKU CAP-28 (ideal cycle 4 seconds) and 1,500 of CAP-38 (ideal 6 seconds). Ideal time for that output is (2,400 × 4 + 1,500 × 6) ÷ 60 = 310 minutes. 180 pieces were rejected.

The short formula gives 65.8% for the same shift: good CAP-28 (2,280 × 4 s) plus good CAP-38 (1,440 × 6 s) is 296 ideal minutes, divided by 450. The 0.1-point gap exists because the rejects are not spread evenly across two SKUs with different cycle times. The short formula is the version worth coding, because it prices each reject at its own SKU's cycle time.

The three tables a plant usually has

shift_plan — line, shift_date, shift, shift_minutes, planned_break_minutes. Planned stops such as scheduled preventive maintenance can go here too if the plant excludes them from OEE; write that decision down.

downtime — line, shift_date, shift, start_time, minutes, reason, loss_type (breakdown, changeover, material_wait, minor_stop).

production — line, shift_date, shift, sku, ideal_cycle_sec, total_count, reject_count.

SQLite on synthetic shift data

CREATE TABLE shift_plan (line TEXT, shift_date TEXT, shift TEXT,
  shift_minutes INT, planned_break_minutes INT);
CREATE TABLE downtime (line TEXT, shift_date TEXT, shift TEXT,
  minutes INT, reason TEXT, loss_type TEXT);
CREATE TABLE production (line TEXT, shift_date TEXT, shift TEXT, sku TEXT,
  ideal_cycle_sec REAL, total_count INT, reject_count INT);

INSERT INTO shift_plan VALUES
 ('L1','2026-09-21','A',480,30), ('L1','2026-09-21','B',480,30),
 ('L2','2026-09-21','A',480,30);
INSERT INTO downtime VALUES
 ('L1','2026-09-21','A',45,'mould jam','breakdown'),
 ('L1','2026-09-21','A',25,'CAP-28 to CAP-38','changeover'),
 ('L1','2026-09-21','B',10,'resin hopper empty','material_wait'),
 ('L2','2026-09-21','A',150,'heater band failure','breakdown');
INSERT INTO production VALUES
 ('L1','2026-09-21','A','CAP-28',4,2400,120),
 ('L1','2026-09-21','A','CAP-38',6,1500,60),
 ('L1','2026-09-21','B','CAP-28',4,5600,90),
 ('L2','2026-09-21','A','CAP-38',6,2600,40);

WITH down AS (
  SELECT line, shift_date, shift, SUM(minutes) AS down_min
  FROM downtime GROUP BY 1,2,3),
prod AS (
  SELECT line, shift_date, shift,
         SUM(total_count) AS total_count,
         SUM(total_count - reject_count) AS good_count,
         SUM(ideal_cycle_sec * total_count) / 60.0 AS ideal_min_total,
         SUM(ideal_cycle_sec * (total_count - reject_count)) / 60.0 AS ideal_min_good
  FROM production GROUP BY 1,2,3),
base AS (
  SELECT p.line, p.shift_date, p.shift,
         p.shift_minutes - p.planned_break_minutes AS planned_min,
         p.shift_minutes - p.planned_break_minutes - COALESCE(d.down_min,0) AS run_min,
         pr.total_count, pr.good_count, pr.ideal_min_total, pr.ideal_min_good
  FROM shift_plan p
  LEFT JOIN down d USING (line, shift_date, shift)
  LEFT JOIN prod pr USING (line, shift_date, shift))
SELECT line, shift,
  ROUND(100.0 * run_min / planned_min, 1) AS availability,
  ROUND(100.0 * ideal_min_total / run_min, 1) AS performance,
  ROUND(100.0 * good_count / total_count, 1) AS quality,
  ROUND(100.0 * ideal_min_good / planned_min, 1) AS oee
FROM base
UNION ALL
SELECT 'ALL', 'rolled-up',
  ROUND(100.0 * SUM(run_min) / SUM(planned_min), 1),
  ROUND(100.0 * SUM(ideal_min_total) / SUM(run_min), 1),
  ROUND(100.0 * SUM(good_count) / SUM(total_count), 1),
  ROUND(100.0 * SUM(ideal_min_good) / SUM(planned_min), 1)
FROM base;

The oee column uses ideal_min_good, so it is not always exactly availability × performance × quality from the same row; the difference is the SKU weighting of rejects described above.

Averaging percentages: the error in most plant decks

The simple mean of the three OEE figures is (65.8 + 81.6 + 56.9) ÷ 3 = 68.1% here only because all three shifts had the same 450 planned minutes. Put an eight-hour L2 shift beside a four-hour trial run on another line and the average gives the trial run the same weight as a full shift. The rolled-up row above sums minutes and counts first, which is always correct. If a management report shows an "average OEE" across lines of different sizes, ask how it was weighted.

Data problems you will meet on the shop floor

• Ideal cycle time that nobody has checked. Master data often holds the rated cycle from commissioning, or a "standard" padded for planning. If performance reads above 100% on any shift, the ideal is wrong for that SKU.
• Minor stops never logged. Operators record a 45-minute breakdown but not twenty 40-second jams. Those show up as low performance, not low availability. A performance loss with no matching downtime rows is a hint to look at PLC counters.
• Rework counted as good. A part reworked and passed is not first-pass good. Decide whether quality means first-pass yield and keep a separate rework count.
• Changeovers moved to "planned". Some plants exclude changeovers from planned production time to lift availability. That hides the loss that SMED projects are meant to attack. Keep changeovers as an availability loss and report them separately.
• Shift boundaries. A breakdown that starts at 21:50 and ends at 22:40 belongs partly to two shifts. Split events at the boundary before summing.

What to put in front of the plant head

A plant head wants to know which loss to attack this week. Rank lines by lost minutes, split into the three buckets, rather than by OEE percentage: L2's heater band failure cost 150 minutes in a single shift, nearly twice all the other logged downtime combined (80 minutes). Then list the top three downtime reasons by minutes across the week. OEE is the scoreboard; the minutes table is the to-do list.

Shift-level SQL like this, with CTEs and weighted roll-ups, gets drilled repeatedly in the Data Analyst programme. Mechanical and production engineers moving into analytics can use a project like this as evidence; see mechanical engineer to data scientist.