Supply chain executive to analyst: OTIF and fill rate in SQL

Belief 1: "My ERP reports already give me the analysis"

What the evidence shows: an ERP OTIF report is a calculation someone configured, with a definition you may never have seen. Is "on time" measured against the requested date, the confirmed date or the promised date? Is "in full" judged per line or per order? Is a delivery on time if it arrived on the date but after the receiving window closed?

The test is to recompute it yourself. Here is a synthetic order-line table and a query that calculates OTIF at line level and at order level.

CREATE TABLE order_lines (
  order_id TEXT,
  line_no INTEGER,
  sku TEXT,
  qty_ordered INTEGER,
  qty_delivered INTEGER,
  promised_date TEXT,
  delivered_date TEXT
);

INSERT INTO order_lines VALUES
('SO-101',1,'A12',100,100,'2026-09-10','2026-09-10'),
('SO-101',2,'B07',50,50,'2026-09-10','2026-09-10'),
('SO-102',1,'A12',80,80,'2026-09-11','2026-09-12'),
('SO-103',1,'C33',200,180,'2026-09-11','2026-09-11'),
('SO-103',2,'B07',40,40,'2026-09-11','2026-09-11'),
('SO-103',3,'A12',60,60,'2026-09-11','2026-09-11'),
('SO-104',1,'C33',120,120,'2026-09-12','2026-09-12'),
('SO-105',1,'B07',30,30,'2026-09-12','2026-09-11'),
('SO-105',2,'A12',90,90,'2026-09-12','2026-09-14');

-- Line-level OTIF: each line judged separately
SELECT ROUND(100.0 * AVG(CASE
         WHEN qty_delivered >= qty_ordered AND delivered_date <= promised_date
         THEN 1 ELSE 0 END), 1) AS line_otif_pct
FROM order_lines;

-- Order-level OTIF: an order counts only if every line is on time and in full
SELECT ROUND(100.0 * AVG(order_ok), 1) AS order_otif_pct
FROM (
  SELECT order_id,
         MIN(CASE WHEN qty_delivered >= qty_ordered AND delivered_date <= promised_date
                  THEN 1 ELSE 0 END) AS order_ok
  FROM order_lines
  GROUP BY order_id
);

On these nine synthetic lines, line-level OTIF is about 67% and order-level OTIF is 40%. Same shipments, two honest numbers, and a customer ordering multi-line orders experiences the second one. That gap is the first thing you would put in a portfolio write-up. Notice also that SO-105 line 1 arrived a day early — some customers penalise early delivery, and this query counts it as on time. State that choice.

Belief 2: "Analytics means I have to learn machine learning first"

What the evidence shows: most early supply chain analytics work is descriptive and diagnostic — service levels, lead-time variability, stock cover, vendor performance, freight cost per kilogram. SQL, a spreadsheet and a dashboard tool cover a large share of it. Forecasting and optimisation come later, and they only make sense once the base data is clean. If you eventually want modelling, read data analytics vs data science before choosing a path.

Belief 3: "I'll have to leave supply chain to become an analyst"

What the evidence shows: your domain knowledge is the part other candidates lack. You know that a GRN may be posted days after physical receipt, that a POD scan date can be the transporter's upload date rather than the delivery date, and that a stock-transfer order inflates dispatch counts. The documents you already handle are ready-made project material:

• Dispatch register and delivery challans — ship date, quantity, vehicle, transporter.
• Proof-of-delivery records — the only reliable delivery timestamp, when captured properly.
• GRN register — supplier lead time from PO date to receipt.
• Inventory ageing and ABC classification — slow-moving and obsolete stock.
• Vendor scorecards and freight bills — cost and reliability by lane.

Belief 4: "My Excel is good enough"

What the evidence shows: Excel skill is real and transferable, but a pivot table on a pasted export cannot be rerun reliably next month, and a VLOOKUP across 2 lakh rows is where errors creep in unnoticed. The gap to close is SQL (joins between order lines, deliveries and SKU master; date arithmetic; window functions for lead-time trends), plus a dashboard that refreshes from the query rather than a copy.

Belief 5: "A bigger dataset makes a better portfolio"

What the evidence shows: a small, well-defined synthetic dataset with explained definitions beats a large public dataset you cannot interpret. A strong first project: three months of synthetic order lines for a distributor, OTIF at both levels by customer and lane, a lead-time distribution per transporter, and one recommendation — for example, which lane's late deliveries come from dispatch delays versus transit delays, stated as a finding with its limitations.

The role to target first

Aim first for supply chain analyst, logistics analyst, procurement analyst or inventory planning analyst roles, where operational credibility counts. A general data analyst role is a realistic next step once you have one project outside logistics. The free skill check will show which analyst skills your resume still does not evidence.

An interview answer written for you

"I spent four years as a logistics executive at a distributor and sent the weekly OTIF report to our key accounts. When one customer disputed our number, I found our ERP measured in-full per line, while they measured per order. I learned SQL to recompute it both ways on a synthetic copy of our data, and the order-level figure was far lower. I want to move into analytics because resolving that kind of definition problem is the part of my job I found most valuable, and supply chain is where I can do it with real understanding."

For structured practice in SQL, Power BI and checking AI-generated analysis before it reaches a stakeholder, see the Data Analyst programme.