Open Purchase Orders Past Their Promised Date

Easy
377.

Open Purchase Orders Past Their Promised Date

EasyDatesBasics

MySQL

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

ColumnTypeValues
po_idint
supplier_idint
order_datedate
promised_datedate
received_datedate
statusenumOPEN, RECEIVED, CANCELLED

Primary key: po_id.

received_date is set only when the goods arrived; it is NULL for open and for cancelled orders.

Example

The rows must come back in the order shown.

PurchaseOrder

po_idsupplier_idorder_datepromised_datereceived_datestatus
20112025-02-202025-03-01NULLOPEN
20222025-02-252025-03-052025-03-04RECEIVED
20322025-03-012025-03-14NULLOPEN
20432025-03-022025-03-15NULLOPEN
20542025-02-102025-02-20NULLCANCELLED
20632025-02-152025-03-01NULLOPEN

Output

po_idsupplier_idpromised_datedays_overdue
20112025-03-0114
20632025-03-0114
20322025-03-141
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.

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

ColumnType
po_idint
supplier_idint
order_datedate
promised_datedate
received_datedate
statusenum(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_idsupplier_idorder_datepromised_datereceived_datestatus
20112025-02-202025-03-01NULLOPEN
20222025-02-252025-03-052025-03-04RECEIVED
20322025-03-012025-03-14NULLOPEN
20432025-03-022025-03-15NULLOPEN
20542025-02-102025-02-20NULLCANCELLED
20632025-02-152025-03-01NULLOPEN

Output

po_idsupplier_idpromised_datedays_overdue
20112025-03-0114
20632025-03-0114
20322025-03-141

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_id

Another 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_id

Another 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 →