Supplier On-Time-In-Full Rate by Order Quarter — SQL Problem
A purchase order is OTIF (on time, in full) when every one of its lines had received at least its ordered quantity by the order's promised_date — receipts…
- Difficulty: Hard
- Topics: Joins, Conditional Logic, Dates
- Dialect: MySQL
- Problem: #395
Problem statement
A purchase order is OTIF (on time, in full) when every one of its lines had received at least its ordered quantity by the order's promised_date — receipts dated on the promised date count, later receipts do not. A line with no on-time receipt makes the whole order miss.
For each supplier and quarter of order_date (labelled like 2024-Q4), return supplier_id, order_quarter, orders, otif_orders and otif_pct = 100 × otif_orders / orders, rounded to 2 decimal places. Order the rows by supplier_id, then by order_quarter.
Tables
Table: SupplierPo
| Column | Type |
|---|---|
| po_id | int |
| supplier_id | int |
| order_date | date |
| promised_date | date |
Primary key: po_id.
One row per purchase order; every order has at least one line.
Table: SupplierPoLine
| Column | Type |
|---|---|
| po_id | int |
| line_no | int |
| qty_ordered | int |
Primary key: po_id, line_no.
The lines of each purchase order.
Table: LineReceipt
| Column | Type |
|---|---|
| receipt_id | int |
| po_id | int |
| line_no | int |
| received_on | date |
| qty | int |
Primary key: receipt_id.
Partial deliveries against a line; a line can have several.
Examples
Example 1
SupplierPo
| po_id | supplier_id | order_date | promised_date |
|---|---|---|---|
| 1 | 7 | 2024-11-04 | 2024-11-12 |
| 2 | 7 | 2024-12-10 | 2024-12-18 |
| 3 | 7 | 2025-01-06 | 2025-01-14 |
| 4 | 9 | 2024-10-21 | 2024-10-30 |
SupplierPoLine
| po_id | line_no | qty_ordered |
|---|---|---|
| 1 | 1 | 100 |
| 1 | 2 | 40 |
| 2 | 1 | 60 |
| 3 | 1 | 25 |
| 4 | 1 | 80 |
| 4 | 2 | 20 |
LineReceipt
| receipt_id | po_id | line_no | received_on | qty |
|---|---|---|---|---|
| 1 | 1 | 1 | 2024-11-10 | 60 |
| 2 | 1 | 1 | 2024-11-12 | 40 |
| 3 | 1 | 2 | 2024-11-11 | 40 |
| 4 | 2 | 1 | 2024-12-17 | 50 |
| 5 | 2 | 1 | 2024-12-20 | 10 |
| 6 | 3 | 1 | 2025-01-13 | 30 |
| 7 | 4 | 1 | 2024-10-29 | 80 |
Output
| supplier_id | order_quarter | orders | otif_orders | otif_pct |
|---|---|---|---|---|
| 7 | 2024-Q4 | 2 | 1 | 50 |
| 7 | 2025-Q1 | 1 | 1 | 100 |
| 9 | 2024-Q4 | 1 | 0 | 0 |
How to solve Supplier On-Time-In-Full Rate by Order Quarter
OTIF is an "all lines" condition, and the cleanest way to evaluate one is to aggregate upwards in levels. At the line level, LEFT JOIN the receipts and sum only those dated on or before the order's promised date — the date test sits inside a CASE within the SUM, because putting it in WHERE would delete lines whose only receipts were late and make them disappear instead of fail. A line is OK when that on-time quantity reaches the ordered quantity; a line with no receipts at all gets 0 through COALESCE and fails.
At the order level, the order is OTIF exactly when every line is OK, which is MIN(line_ok) = 1. Finally the orders are grouped by supplier and a quarter label CONCAT(YEAR(order_date), '-Q', QUARTER(order_date)), and the rate is 100 × OTIF orders / orders, rounded to two places so MySQL's four-decimal division agrees with other engines.
The alternative states the order-level rule as its negation: an order misses if there exists a line whose on-time receipts fall short. A correlated subquery sums the on-time receipts per line. It reads naturally but probes receipts per line per order; the staged version is a few hash aggregations.
Reference solution (MySQL)
WITH line_status AS (
SELECT l.po_id,
CASE WHEN COALESCE(SUM(CASE WHEN r.received_on <= p.promised_date THEN r.qty ELSE 0 END), 0) >= l.qty_ordered
THEN 1 ELSE 0 END AS line_ok
FROM SupplierPoLine l
JOIN SupplierPo p ON p.po_id = l.po_id
LEFT JOIN LineReceipt r ON r.po_id = l.po_id AND r.line_no = l.line_no
GROUP BY l.po_id, l.line_no, l.qty_ordered
), po_status AS (
SELECT po_id, MIN(line_ok) AS is_otif FROM line_status GROUP BY po_id
)
SELECT p.supplier_id,
CONCAT(YEAR(p.order_date), '-Q', QUARTER(p.order_date)) AS order_quarter,
COUNT(*) AS orders,
SUM(s.is_otif) AS otif_orders,
ROUND(100 * SUM(s.is_otif) / COUNT(*), 2) AS otif_pct
FROM SupplierPo p
JOIN po_status s ON s.po_id = p.po_id
GROUP BY p.supplier_id, CONCAT(YEAR(p.order_date), '-Q', QUARTER(p.order_date))
ORDER BY p.supplier_id, order_quarterAnother way
SELECT supplier_id, order_quarter, COUNT(*) AS orders, SUM(is_otif) AS otif_orders, ROUND(100 * SUM(is_otif) / COUNT(*), 2) AS otif_pct FROM (
SELECT p.supplier_id, CONCAT(YEAR(p.order_date), '-Q', QUARTER(p.order_date)) AS order_quarter,
CASE WHEN EXISTS (
SELECT 1 FROM SupplierPoLine l
WHERE l.po_id = p.po_id
AND l.qty_ordered > (SELECT IFNULL(SUM(r.qty), 0) FROM LineReceipt r
WHERE r.po_id = l.po_id AND r.line_no = l.line_no AND r.received_on <= p.promised_date)
) THEN 0 ELSE 1 END AS is_otif
FROM SupplierPo p) t
GROUP BY supplier_id, order_quarter
ORDER BY supplier_id, order_quarter← Median Supplier Lead Time From Goods Inward · Daily Stock Carried Forward From the Last Count →