Find delayed orders in your operations data
Build an operations report that separates orders still waiting from orders that shipped late.
By the Datarelix Team · Published · Updated · Last verified
“Delayed orders” can mean orders not yet dispatched, orders dispatched after their promise date or deliveries that arrived late. Those are different reports. Choose the event your team can act on before asking for a total; you can then ask the operations database in plain English and inspect the rows that come back.
This example finds orders still waiting for dispatch at a fixed cutoff. You need permission to read order IDs, payment, promised dispatch and actual dispatch timestamps; customer contact details are not needed for this first report.
Define the cutoff and exclusions
Use September 8, 2026 at 00:00 UTC as the cutoff. Include orders paid before then, promised for dispatch before then and not dispatched before then. Exclude cancelled orders. A missing promise date is a data-quality case to report separately, not proof of an on-time order.
| Order | Promised dispatch, UTC | Actual dispatch, UTC | Included at cutoff? |
|---|---|---|---|
| 1042 | Sep 5, 10:00 | Not recorded | Yes |
| 1056 | Sep 7, 16:00 | Sep 8, 09:00 | Yes; dispatched after cutoff |
| 1061 | Sep 7, 12:00 | Sep 7, 11:00 | No; already dispatched |
| 1070 | Sep 8, 00:00 | Not recorded | No; promise is at the cutoff |
All four orders are paid and not cancelled. The expected backlog is two orders. The table intentionally includes boundary cases so the definition can be checked.
Ask for rows you can review
At September 8, 2026 00:00 UTC, list paid, non-cancelled orders promised before that time with no dispatch before that time. Show order ID, promised dispatch and actual dispatch. Sort the oldest promises first.
Example PostgreSQL:
SELECT order_id, ship_by, shipped_at
FROM orders
WHERE status <> 'cancelled'
AND paid_at < TIMESTAMPTZ '2026-09-08 00:00:00+00'
AND ship_by < TIMESTAMPTZ '2026-09-08 00:00:00+00'
AND (shipped_at IS NULL
OR shipped_at >= TIMESTAMPTZ '2026-09-08 00:00:00+00')
ORDER BY ship_by, order_id;
This uses the stored cancellation status. If cancellations can occur after the cutoff and the current status overwrites history, reconstructing the exact historical backlog requires status history. Say so in the report rather than treating today’s status as a historical record. The query is derived from the wording of the request, as how text-to-SQL generates database queries shows, so state the cutoff and its time zone explicitly.
For split shipments, decide whether the first shipment or full dispatch closes the order. An order-level timestamp may not be enough. Ask the source owner which event records that decision.
Compare periods without changing the definition
A backlog count is a snapshot. A late-dispatch rate is a measure over a cohort. For a weekly rate, define the denominator as eligible orders promised in that week, and the numerator as those dispatched late or still undispatched at the review cutoff. Use comparable review windows for both weeks.
For example, 8 delayed orders out of 100 eligible orders is 8%; 12 out of 200 is 6%. The second week has more delayed orders but a lower delay rate. Show both the count and denominator before drawing an operational conclusion.
Segment by warehouse or carrier only if the data assigns those fields consistently. The result may identify where to investigate; it does not establish why the delay occurred.
Review the exception list before assigning work
The operations owner defines the promise and dispatch events. The analyst checks whether status history, split shipments or duplicate event records affect the result. Agree on the report’s cutoff before comparing it with the order-management screen, which may show a later state.
Check the oldest included order and an order excluded at the boundary, and apply the join and filter checks from verify AI-generated SQL and results before the list is used. Report missing promise dates separately, and keep unknown statuses visible for investigation instead of quietly classifying them as on time. The useful deliverable is a list of reviewable order IDs and the reason each was included, followed by counts that reconcile to that list.
Create a view for the team meeting
In Datarelix, inspect the returned rows and generated SQL, then ask for counts by promised dispatch date. Check that the chart totals match the table. Save a reviewed chart to a private dashboard, as build a dashboard from database questions shows; a manual refresh reruns the saved query.
The fixed cutoff above preserves the report’s question. For today’s backlog, request a relative cutoff and specify its time zone. If you use a replica, check its freshness before treating a recently shipped order as still waiting.
Datarelix does not update orders or notify customers. Use the checked IDs in your authorised operations workflow. Start with the MySQL reporting guide or connect another supported source.