Open Purchase Orders Past Their Promised Date — SQL Problem
The buying desk reviews late deliveries every Saturday; today is 2025-03-15.
- Difficulty: Easy
- Topics: Dates, Basics
- Dialect: MySQL
- Problem: #377
Problem statement
The buying desk reviews late deliveries every Saturday; today is 2025-03-15. A purchase order is overdue when it is still OPEN and its promised delivery date is before today. Received and cancelled orders are never overdue, even if they have no received date.
Return po_id, supplier_id, promised_date and days_overdue (the number of days from the promised date to 2025-03-15). Order the rows by days_overdue highest first, then by po_id.
Tables
Table: PurchaseOrder
| Column | Type |
|---|---|
| po_id | int |
| supplier_id | int |
| order_date | date |
| promised_date | date |
| received_date | date |
| status | enum(OPEN, RECEIVED, CANCELLED) |
Primary key: po_id.
received_date is set only when the goods arrived; it is NULL for open and for cancelled orders.
Examples
Example 1
PurchaseOrder
| po_id | supplier_id | order_date | promised_date | received_date | status |
|---|---|---|---|---|---|
| 201 | 1 | 2025-02-20 | 2025-03-01 | NULL | OPEN |
| 202 | 2 | 2025-02-25 | 2025-03-05 | 2025-03-04 | RECEIVED |
| 203 | 2 | 2025-03-01 | 2025-03-14 | NULL | OPEN |
| 204 | 3 | 2025-03-02 | 2025-03-15 | NULL | OPEN |
| 205 | 4 | 2025-02-10 | 2025-02-20 | NULL | CANCELLED |
| 206 | 3 | 2025-02-15 | 2025-03-01 | NULL | OPEN |
Output
| po_id | supplier_id | promised_date | days_overdue |
|---|---|---|---|
| 201 | 1 | 2025-03-01 | 14 |
| 206 | 3 | 2025-03-01 | 14 |
| 203 | 2 | 2025-03-14 | 1 |
How to solve Open Purchase Orders Past Their Promised Date
Two conditions decide whether an order is overdue: it must still be open, and its promised date must be strictly before the review date. The status test is the one people skip — they filter on received_date IS NULL, which also catches cancelled orders that will never arrive and should not be chased.
The date test is strict: an order promised for 2025-03-15 is due today, not late. Dates are stored as YYYY-MM-DD, so comparing them as values (or as text) orders them correctly. DATEDIFF('2025-03-15', promised_date) then counts the days of delay — the later date goes first so the number is positive. TIMESTAMPDIFF(DAY, promised_date, '2025-03-15') gives the same count with the arguments the other way round.
Because many orders can be equally late, po_id is the final sort key. The query is one pass over the table; an index on (status, promised_date) would serve it directly.
Reference solution (MySQL)
SELECT po_id, supplier_id, promised_date,
DATEDIFF('2025-03-15', promised_date) AS days_overdue
FROM PurchaseOrder
WHERE status = 'OPEN'
AND promised_date < '2025-03-15'
ORDER BY days_overdue DESC, po_idAnother way
SELECT po_id, supplier_id, promised_date, DATEDIFF('2025-03-15', promised_date) AS days_overdue FROM PurchaseOrder WHERE status = 'OPEN' AND DATEDIFF('2025-03-15', promised_date) > 0 ORDER BY days_overdue DESC, po_idAnother way
SELECT po_id, supplier_id, promised_date, TIMESTAMPDIFF(DAY, promised_date, '2025-03-15') AS days_overdue FROM PurchaseOrder WHERE status = 'OPEN' AND promised_date <= DATE_SUB('2025-03-15', INTERVAL 1 DAY) ORDER BY days_overdue DESC, po_id← Category Codes From SKU Prefixes · Net Stock Change per SKU From the Movement Log →