SQL CASE: Classify Rows With an Explicit Boundary Policy

The practice task

Classify fictional service waits as unknown, urgent, review or routine. A 60-minute wait should be urgent and a 30-minute wait should be review. Keep those boundaries visible in the result.

The data below is fictional. Run the example in a new SQLite database. It was checked with SQLite 3.51.0; other databases may use different syntax, functions or type rules.

Complete worked query

Create the small fixture and run the query together. The IDs, boundary values and missing entries make the result small enough to check by hand.

CREATE TABLE waits(id INTEGER, minutes INTEGER);
INSERT INTO waits VALUES (1,NULL),(2,15),(3,30),(4,60),(5,75);

SELECT id, CASE
  WHEN minutes IS NULL THEN 'unknown'
  WHEN minutes >= 60 THEN 'urgent'
  WHEN minutes >= 30 THEN 'review'
  ELSE 'routine' END AS band
FROM waits ORDER BY id;

Checked result

This is the result of the exact example above. NULL marks a missing database value rather than the text string "NULL".

Read the result

The most restrictive measured condition appears first. A 75-minute wait meets both numeric thresholds, but its first qualifying branch is urgent. Placing the 30-minute branch first would classify it as review instead.

The missing-value branch makes an incomplete measurement distinguishable from a routine wait. ELSE is an explicit policy for the remaining measured values in this exercise. If negative waits are possible in the source, add validation rather than silently accepting them as routine.

CASE returns a value; it is not the same as a procedural IF statement that runs a sequence of commands. Use the expression in a result column, aggregate or sort condition where a value is required. Keep the categories consistent so another reader can reconstruct their boundaries.

Mistakes to check

Use these checks when adapting the example to a different dataset.

• Put overlapping thresholds in the intended priority order.
• Test values exactly on the boundary.
• Choose compatible result types for the branches.