IntermediateDate & Time Functions

Calculate SLA breach: orders not shipped in 3 days

The query

SQL

SELECT
  order_id,
  cust_id,
  order_date,
  shipped_at,
  COALESCE(shipped_at::DATE, CURRENT_DATE) - order_date::DATE AS days_to_ship,
  CASE
    WHEN shipped_at IS NULL THEN 'Pending'
    WHEN shipped_at::DATE - order_date::DATE > 3 THEN 'SLA Breach'
    ELSE 'On Time'
  END AS sla_status
FROM orders
WHERE status IN ('shipped', 'pending')
ORDER BY days_to_ship DESC;

Tested against PostgreSQL 16

Note

“SLA tracking: compute days difference and classify. COALESCE handles unshipped orders.

Tables referenced

orders6 cols