Purchase Order Lines Received Short

Medium
382.

Purchase Order Lines Received Short

Suppliers often deliver a purchase order line in several goods receipts (GRNs). A line is short when the total quantity received across all its GRNs is less than the quantity ordered; a line with no GRN at all is short by its whole quantity. Over-deliveries are not short.

Return po_id, line_no, sku, qty_ordered, qty_received (0 when nothing arrived) and shortfall (qty_ordered minus qty_received) for every short line. Order the rows by po_id, then by line_no.

Tables

Table: PoLine

ColumnType
po_idint
line_noint
skuvarchar
qty_orderedint

Primary key (together): po_id, line_no.

One row per line of a purchase order.

Table: GoodsReceipt

ColumnType
grn_idint
po_idint
line_noint
qty_receivedint
received_ondate

Primary key: grn_id.

One row per delivery against a PO line; (po_id, line_no) always exists in PoLine.

Example

The rows must come back in the order shown.

PoLine

po_idline_noskuqty_ordered
5011GRO-1001100
5012GRO-100260
5021DAI-200140
5022SNK-6001200
5031HOM-500230

GoodsReceipt

grn_idpo_idline_noqty_receivedreceived_on
15011602025-02-03
25011402025-02-06
35012452025-02-03
45021442025-02-05
550221202025-02-05
65022502025-02-09

Output

po_idline_noskuqty_orderedqty_receivedshortfall
5012GRO-1002604515
5022SNK-600120017030
5031HOM-500230030
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

Purchase Order Lines Received Short — SQL Joins Problem

Suppliers often deliver a purchase order line in several goods receipts (GRNs).

  • Difficulty: Medium
  • Topics: Joins, Aggregation, Subqueries
  • Dialect: MySQL
  • Problem: #382

Problem statement

Suppliers often deliver a purchase order line in several goods receipts (GRNs). A line is short when the total quantity received across all its GRNs is less than the quantity ordered; a line with no GRN at all is short by its whole quantity. Over-deliveries are not short.

Return po_id, line_no, sku, qty_ordered, qty_received (0 when nothing arrived) and shortfall (qty_ordered minus qty_received) for every short line. Order the rows by po_id, then by line_no.

Tables

Table: PoLine

ColumnType
po_idint
line_noint
skuvarchar
qty_orderedint

Primary key: po_id, line_no.

One row per line of a purchase order.

Table: GoodsReceipt

ColumnType
grn_idint
po_idint
line_noint
qty_receivedint
received_ondate

Primary key: grn_id.

One row per delivery against a PO line; (po_id, line_no) always exists in PoLine.

Examples

Example 1

PoLine

po_idline_noskuqty_ordered
5011GRO-1001100
5012GRO-100260
5021DAI-200140
5022SNK-6001200
5031HOM-500230

GoodsReceipt

grn_idpo_idline_noqty_receivedreceived_on
15011602025-02-03
25011402025-02-06
35012452025-02-03
45021442025-02-05
550221202025-02-05
65022502025-02-09

Output

po_idline_noskuqty_orderedqty_receivedshortfall
5012GRO-1002604515
5022SNK-600120017030
5031HOM-500230030

How to solve Purchase Order Lines Received Short

The comparison is between one ordered quantity and the sum of any number of receipts, so the receipts are aggregated first: a derived table groups GoodsReceipt by (po_id, line_no) and sums the quantities. That table is LEFT JOINed to PoLine on both key columns, so every line stays — a line nothing was delivered against gets a NULL sum, which COALESCE turns into 0.

With one row per line, the filter received < qty_ordered keeps the short lines and drops exact and over-deliveries. The shortfall is a subtraction on the same row.

Joining the raw receipts and grouping afterwards (the first alternative) gives the same numbers because each line's group then holds exactly its own receipts; the HAVING clause does the filtering. A correlated scalar subquery per line works too. The common bug is joining on po_id alone, which multiplies every line by every receipt of the whole order and inflates the sums. All forms are linear in the two tables with an index on GoodsReceipt(po_id, line_no).

Reference solution (MySQL)

SELECT l.po_id, l.line_no, l.sku, l.qty_ordered,
       COALESCE(r.received, 0) AS qty_received,
       l.qty_ordered - COALESCE(r.received, 0) AS shortfall
FROM PoLine l
LEFT JOIN (
  SELECT po_id, line_no, SUM(qty_received) AS received
  FROM GoodsReceipt
  GROUP BY po_id, line_no
) r ON r.po_id = l.po_id AND r.line_no = l.line_no
WHERE COALESCE(r.received, 0) < l.qty_ordered
ORDER BY l.po_id, l.line_no

Another way

SELECT l.po_id, l.line_no, l.sku, l.qty_ordered,
       COALESCE(SUM(g.qty_received), 0) AS qty_received,
       l.qty_ordered - COALESCE(SUM(g.qty_received), 0) AS shortfall
FROM PoLine l
LEFT JOIN GoodsReceipt g ON g.po_id = l.po_id AND g.line_no = l.line_no
GROUP BY l.po_id, l.line_no, l.sku, l.qty_ordered
HAVING COALESCE(SUM(g.qty_received), 0) < l.qty_ordered
ORDER BY l.po_id, l.line_no

Another way

SELECT po_id, line_no, sku, qty_ordered, qty_received, qty_ordered - qty_received AS shortfall FROM (
  SELECT l.po_id, l.line_no, l.sku, l.qty_ordered,
         (SELECT IFNULL(SUM(g.qty_received), 0) FROM GoodsReceipt g WHERE g.po_id = l.po_id AND g.line_no = l.line_no) AS qty_received
  FROM PoLine l) t
WHERE qty_received < qty_ordered
ORDER BY po_id, line_no

← Supplier Average Lead Time in Days · Store Stock-Out Rate From Daily Closing Stock →