Late Work Order Rate by Line — SQL Dates Problem
A work order is late when it was completed on a calendar day after its due_date; completing it any time on the due date itself is on time.
- Difficulty: Medium
- Topics: Dates, Conditional Logic, Aggregation
- Dialect: MySQL
- Problem: #259
Problem statement
A work order is late when it was completed on a calendar day after its due_date; completing it any time on the due date itself is on time. Orders not yet completed (completed_at NULL) are left out of every number.
For every line with at least one completed order, return line_code, completed_orders, late_orders and late_pct — late orders as a percentage of completed orders, rounded to two decimals. Order by late_pct from highest to lowest, then by line_code.
Tables
Table: LineWorkOrder
| Column | Type |
|---|---|
| work_order_id | int |
| line_code | varchar |
| due_date | date |
| completed_at | datetime |
Primary key: work_order_id.
completed_at is NULL while an order is open.
Examples
Example 1
LineWorkOrder
| work_order_id | line_code | due_date | completed_at |
|---|---|---|---|
| 9001 | L1-ASSY | 2024-10-05 | 2024-10-05 22:40:00 |
| 9002 | L1-ASSY | 2024-10-07 | 2024-10-08 01:15:00 |
| 9003 | L1-ASSY | 2024-10-09 | 2024-10-08 16:00:00 |
| 9004 | L4-WELD | 2024-10-06 | 2024-10-10 09:30:00 |
| 9005 | L4-WELD | 2024-10-12 | NULL |
| 9006 | L5-PACK | 2024-10-15 | 2024-10-14 11:00:00 |
| 9007 | L3-PAINT | 2024-10-20 | NULL |
Output
| line_code | completed_orders | late_orders | late_pct |
|---|---|---|---|
| L4-WELD | 1 | 1 | 100 |
| L1-ASSY | 3 | 1 | 33.33 |
| L5-PACK | 1 | 0 | 0 |
How to solve Late Work Order Rate by Line
The trap is the type mismatch. completed_at is a datetime and due_date a date; compared directly, '2024-10-05 22:40:00' > '2024-10-05' is true, so an order finished on its due date would be called late. Reduce the timestamp to its day with DATE(completed_at) before comparing, or use DATEDIFF(completed_at, due_date) > 0, which compares calendar days, or ask whether it finished at or after midnight of the next day.
With that settled it is conditional aggregation: drop open orders in WHERE, group by line, COUNT(*) the completed ones and SUM(CASE … THEN 1 ELSE 0 END) the late ones, and divide for the percentage, rounded to two decimals. A line whose orders are all open has no rows after the filter and is not listed, which also keeps the division away from zero.
The order is by the late rate, worst line first, ties alphabetical. One scan and one small aggregation.
Reference solution (MySQL)
SELECT line_code,
COUNT(*) AS completed_orders,
SUM(CASE WHEN DATE(completed_at) > due_date THEN 1 ELSE 0 END) AS late_orders,
ROUND(100 * SUM(CASE WHEN DATE(completed_at) > due_date THEN 1 ELSE 0 END) / COUNT(*), 2) AS late_pct
FROM LineWorkOrder
WHERE completed_at IS NOT NULL
GROUP BY line_code
ORDER BY late_pct DESC, line_codeAnother way
SELECT line_code, COUNT(completed_at) AS completed_orders,
SUM(IF(DATEDIFF(completed_at, due_date) > 0, 1, 0)) AS late_orders,
ROUND(100 * SUM(IF(DATEDIFF(completed_at, due_date) > 0, 1, 0)) / COUNT(completed_at), 2) AS late_pct
FROM LineWorkOrder GROUP BY line_code HAVING COUNT(completed_at) > 0
ORDER BY late_pct DESC, line_codeAnother way
SELECT line_code, COUNT(*) AS completed_orders,
SUM(CASE WHEN completed_at >= DATE_ADD(due_date, INTERVAL 1 DAY) THEN 1 ELSE 0 END) AS late_orders,
ROUND(100 * SUM(CASE WHEN completed_at >= DATE_ADD(due_date, INTERVAL 1 DAY) THEN 1 ELSE 0 END) / COUNT(*), 2) AS late_pct
FROM LineWorkOrder WHERE completed_at IS NOT NULL GROUP BY line_code
ORDER BY late_pct DESC, line_code← Top Operators per Shift by Good Output · Machines Due for Preventive Maintenance by Quarter End →