The searched CASE returns results when a boolean expression is TRUE.

(This differs from the simple case, which can only check for equivalency with an input.)

SELECT Id, ItemId, Price,
  CASE WHEN Price < 10 THEN 'CHEAP'
       WHEN Price < 20 THEN 'AFFORDABLE'
       ELSE 'EXPENSIVE'
  END AS PriceRating
FROM ItemSales

Id | ItemId | Price | PriceRating —|———|—––|———–– 1 | 100 | 34.5 | EXPENSIVE 2 | 145 | 2.3 | CHEAP 3 | 100 | 34.5 | EXPENSIVE 4 | 100 | 34.5 | EXPENSIVE 5 | 145 | 10 | AFFORDABLE