CASE statements

01The problem

Operations wants to know how reliable Harbourline really is:

"For each transport mode, how many deliveries arrived on time, and how many were late?"

The database stores dates, not a column that says "late". You need to create that label from the data: a delivery is late when it took longer than the route's target.

02The concept

CASE returns different values depending on conditions, like IF in Excel.

CASE
  WHEN condition_1 THEN value_1
  WHEN condition_2 THEN value_2
  ELSE value_otherwise
END

• Conditions are checked from top to bottom, and the first one that's true wins.
• If nothing matches and there's no ELSE, the result is NULL.
• A CASE expression can go anywhere a value can: in SELECT, WHERE, GROUP BY or inside an aggregate.

03Example

Label shipments by size:

SELECT
  shipment_id,
  containers,
  CASE
    WHEN containers >= 6 THEN 'Large'
    WHEN containers >= 3 THEN 'Medium'
    ELSE 'Small'
  END AS size_band
FROM shipments
LIMIT 20;

04Walkthrough

For each row, the database checks containers >= 6 first. If that's false it checks containers >= 3, and if neither is true, it falls to ELSE. A 4-container shipment fails the first test and passes the second, so it's 'Medium'. The order matters: if you tested >= 3 first, every large shipment would be labelled 'Medium'.

Now Kemi's question. julianday() turns a date into a day number, so subtracting two of them gives the days between. A delivery is on time when the actual transit is no more than the target:

SELECT
  r.mode,
  COUNT(*) AS delivered,
  SUM(CASE WHEN julianday(s.delivery_date) - julianday(s.ship_date) <= r.target_transit_days THEN 1 ELSE 0 END) AS on_time,
  SUM(CASE WHEN julianday(s.delivery_date) - julianday(s.ship_date) >  r.target_transit_days THEN 1 ELSE 0 END) AS late
FROM shipments AS s
JOIN routes AS r ON r.route_id = s.route_id
WHERE s.status = 'Delivered'
GROUP BY r.mode;

SUM(CASE WHEN … THEN 1 ELSE 0 END) is one of the most useful patterns in SQL: it counts the rows that meet a condition, inside a GROUP BY, without a separate query for each group.

05Practice

06Challenge

07Check your understanding