OTIF at order-line level: why on-time and in-full must be one test

The question a distributor is really asking

A modern-trade buyer, a quick-commerce purchase team or a large retail chain places a purchase order with a promised date. When the truck arrives short, or a day late, the shelf is empty regardless of which failure happened. Buyers often write OTIF targets and short-supply penalties into vendor terms, so the supply-chain head wants three answers:

• What share of lines did we fulfil on time and in full last month?
• Which SKUs, depots and transporters cause the misses?
• Is the problem stock (in-full) or dispatch and transit (on-time)?

The third question is why teams track the components separately. The trap is then recombining them.

Tables you will usually be handed

Order data comes from the ERP or distributor management system; delivery data from proof-of-delivery scans or goods-receipt confirmations sent back by the buyer.

The grain of deliveries matters. A line shipped in two trips has two rows, and any metric built on the last delivery or on a single join row will misjudge it.

Formulas, stated so nobody can argue later

For each line:

• qty_on_time = sum of qty_delivered where delivered_date ≤ promised_date
• Line OTIF flag = 1 if qty_on_time ≥ qty_ordered, else 0
• Line OTIF % = sum of OTIF flags ÷ number of lines due in the period × 100
• Order OTIF % = orders where every line passes ÷ orders due × 100

Diagnostic components, reported beside OTIF but never multiplied into it:

• On-time % = lines whose first delivery arrived by the promised date ÷ lines due
• In-full % = lines where total delivered eventually reached qty_ordered ÷ lines due

Worked example on synthetic distributor data

Five orders and nine lines, all synthetic. SO-101 line 2 went out 54 of 60 units on the promised day and the remaining 6 two days later. SO-103 arrived a day late. SO-105 line 2 was never shipped.

-- Synthetic data: 5 orders, 9 lines, one FMCG distributor
CREATE TABLE order_lines (
  order_id TEXT, line_no INTEGER, sku TEXT,
  qty_ordered INTEGER, promised_date TEXT
);
CREATE TABLE deliveries (
  order_id TEXT, line_no INTEGER, delivered_date TEXT, qty_delivered INTEGER
);
INSERT INTO order_lines VALUES
 ('SO-101',1,'ATTA-5KG',40,'2026-09-02'),
 ('SO-101',2,'OIL-1L',60,'2026-09-02'),
 ('SO-102',1,'ATTA-5KG',25,'2026-09-03'),
 ('SO-102',2,'SUGAR-1KG',50,'2026-09-03'),
 ('SO-103',1,'OIL-1L',30,'2026-09-04'),
 ('SO-104',1,'TEA-250G',80,'2026-09-05'),
 ('SO-104',2,'SUGAR-1KG',20,'2026-09-05'),
 ('SO-105',1,'ATTA-5KG',35,'2026-09-06'),
 ('SO-105',2,'TEA-250G',15,'2026-09-06');
INSERT INTO deliveries VALUES
 ('SO-101',1,'2026-09-02',40),
 ('SO-101',2,'2026-09-02',54),
 ('SO-101',2,'2026-09-04',6),
 ('SO-102',1,'2026-09-03',25),
 ('SO-102',2,'2026-09-03',50),
 ('SO-103',1,'2026-09-05',30),
 ('SO-104',1,'2026-09-05',80),
 ('SO-104',2,'2026-09-05',20),
 ('SO-105',1,'2026-09-06',35);

WITH line_status AS (
  SELECT l.order_id, l.line_no, l.qty_ordered,
         COALESCE(SUM(CASE WHEN d.delivered_date <= l.promised_date
                           THEN d.qty_delivered END), 0) AS qty_on_time,
         COALESCE(SUM(d.qty_delivered), 0) AS qty_total
  FROM order_lines l
  LEFT JOIN deliveries d
    ON d.order_id = l.order_id AND d.line_no = l.line_no
  GROUP BY l.order_id, l.line_no, l.qty_ordered
)
SELECT order_id, line_no, qty_ordered, qty_on_time, qty_total,
       CASE WHEN qty_on_time >= qty_ordered THEN 1 ELSE 0 END AS otif_line
FROM line_status
ORDER BY order_id, line_no;

The LEFT JOIN keeps SO-105 line 2, which has no delivery row at all. An inner join would silently drop it and push OTIF up. Now the summary, including the component rates and the tempting shortcut:

WITH line_status AS (
  SELECT l.order_id, l.line_no, l.qty_ordered,
         MIN(d.delivered_date) AS first_delivery, l.promised_date,
         COALESCE(SUM(CASE WHEN d.delivered_date <= l.promised_date
                           THEN d.qty_delivered END), 0) AS qty_on_time,
         COALESCE(SUM(d.qty_delivered), 0) AS qty_total
  FROM order_lines l
  LEFT JOIN deliveries d
    ON d.order_id = l.order_id AND d.line_no = l.line_no
  GROUP BY l.order_id, l.line_no, l.qty_ordered, l.promised_date
),
flags AS (
  SELECT order_id,
         CASE WHEN first_delivery <= promised_date THEN 1 ELSE 0 END AS on_time,
         CASE WHEN qty_total >= qty_ordered THEN 1 ELSE 0 END AS in_full,
         CASE WHEN qty_on_time >= qty_ordered THEN 1 ELSE 0 END AS otif
  FROM line_status
)
SELECT
  ROUND(100.0 * AVG(on_time), 1) AS on_time_pct,
  ROUND(100.0 * AVG(in_full), 1) AS in_full_pct,
  ROUND(AVG(on_time) * AVG(in_full) * 100, 1) AS multiplied_pct,
  ROUND(100.0 * AVG(otif), 1) AS line_otif_pct,
  (SELECT ROUND(100.0 * AVG(all_ok), 1)
     FROM (SELECT MIN(otif) AS all_ok FROM flags GROUP BY order_id)) AS order_otif_pct
FROM flags;

The result: on-time 77.8%, in-full 88.9%, their product 69.1%, true line OTIF 66.7%, and order OTIF 40.0%. The product is wrong because SO-101 line 2 counts as "on time" (its first trip was on time) and "in full" (it was completed eventually), yet it fails the real test. Only two of five orders arrived complete and on time.

Five ways OTIF gets inflated in Indian distribution data

1. 1Promised date overwritten by amendments. If the ERP updates promised_date when a salesperson agrees a delay, every late line becomes on time. Measure against the original promise and report re-promised lines separately using order_amendments.
2. 2Quantity cut before dispatch. A depot short on stock reduces qty_ordered to what it has. Use the quantity at order acceptance, not the dispatched quantity.
3. 3Delivered date taken from the invoice. Invoice date is when goods left, not when the buyer received them. Use POD or the customer's goods-receipt date, and say which one you used.
4. 4Cancelled lines excluded. A line the customer cancelled because it was already late is a failure, not a non-event. Keep supplier-caused cancellations in the denominator.
5. 5Unit mismatches. Orders in cases, deliveries in pieces. Convert both to one unit using the SKU master before comparing.

What the supply-chain head should see first

Lead with one sentence: "Line OTIF was 66.7%; order OTIF, which is what the buyer feels, was 40%." Then a small table splitting the misses by cause: late only, short only, both, not shipped. Each miss should trace to a depot and SKU, so the next meeting is about fixing a specific stock-out or a specific transporter rather than debating the headline.

Keep on-time and in-full as columns in that table, clearly labelled as diagnostics, and never show their product. Add a four-week trend so a single bad week is not over-read.

If you want to practise turning raw ERP extracts into definitions like this, the Data Analyst programme spends much of its 3–4 months on that kind of SQL. On the warehouse side, see measuring pick rate fairly.