SQL - Case

SQL CASE is a very unique conditional statement providing if/then/else logic for any ordinary SQL command, such as SELECT or UPDATE. It then provides when-then-else functionality (WHEN this condition is met THEN do_this).


This functionality provides the developer the ability to manipulate the presentation of the data without actually updating or changing the data as it exists inside the SQL table.

SQL Select Case Code:

USE mydatabase;

SELECT product,
      'Status' = CASE
        WHEN quantity > 0 THEN 'in stock'
        ELSE 'out of stock'
        END
FROM dbo.inventory;

SQL Results:

product	Status
19" LCD Screen	in stock
HP Printer	in stock
Pen	in stock
Stapler	in stock
Hanging Files	in stock
Laptop	in stock

Using the CASE command, we've successfully masked the actual value of the product inventory without actually altering any data. This would be a great way to implement some feature in an online catalog to allow users to check the status of items without disclosing the actual amount of inventory the store currently has in stock.

SQL - Case: Real World Example

As a store owner, there might be a time when you would like to offer sale prices for products. This is a perfect opportunity to write a CASE query and alter the inventory sale prices at the presentation level rather than actually changing the price inside of the inventory table. CASE provides a way for the store owner to mask the data but still present it in a useful format.
Let's back up a second and pull a listing of our recent orders and join this with the inventory table so that the results contain both the quantity of items purchased and the price from the inventory table. To accomplish this we will need to first write a SQL JOIN query.

SQL Join Query:

USE mydatabase;

SELECT dbo.orders.id,
  dbo.orders.customer,
  dbo.orders.quantity,
  dbo.inventory.product,
  dbo.inventory.price
FROM orders
JOIN inventory
ON orders.product = inventory.product

In order to provide results that are much clearer, we've moved away from selecting every column with (*). Instead, we've listed each column that will be of use for the next few steps. Also, let's plan on offering a 25^% off sale on these items.

SQL Results:

id	customer	quantity	product	price
1	Tia	11	Hanging Files	14.99
2	Tia	3	Stapler	7.99
3	A+Maintenance	14	Hanging Files	14.99
4	Gerald Garner	5	19" LCD Screen	179.99
5	Tia	5	19" LCD Screen	179.99
6	Tia	4	HP Printer	89.99

Next we need to look at reducing the prices of the items according to our sale price. For the purpose of this exercise, let's offer a 25^% discount on all our currently pending orders using a SQL CASE query.

SQL Select Case Code:

USE mydatabase;

SELECT dbo.orders.id,
  dbo.orders.customer,
  dbo.orders.quantity,
  dbo.inventory.product,
  dbo.inventory.price,
 'SALE_PRICE' = CASE
   WHEN price > 0 THEN (price * .75)
          END
FROM orders 
JOIN inventory
ON orders.product = inventory.product

Multiplying the current price by .75 reduces the price by approximately 25^%, successfully applying the changes we would like to see but doing so without actually changing any data.

SQL Results:

id	customer	quantity	product	price	SALE_PRICE
1	Tia	11	Hanging Files	14.99	11.2425
2	Tia	3	Stapler	7.99	5.9925
3	A+Maintenance	14	Hanging Files	14.99	11.2425
4	Gerald Garner	5	19" LCD Screen	179.99	134.9925
5	Tia	5	19" LCD Screen	179.99	134.9925
6	Tia	4	HP Printer	89.99	67.4925

The results speak for themselves as the records returned indicate a new table column with the calculated sales price now listed at the end of each row.
Since SQL CASE offers a conditional statement (price > 0), it wouldn't take much more effort to create some conditional statements based on how many products each customer had ordered and offer different discounts based on the volume of a customer order.
For instance, as a web-company, maybe we would like to offer an additional 10^% discount to orders totaling more than ^$100. We could accomplish this in a very similar fashion.

SQL Results:

USE mydatabase;

SELECT dbo.orders.id,
  dbo.orders.customer,
  dbo.orders.quantity,
  dbo.inventory.product,
  dbo.inventory.price,
 'SALE_PRICE' = CASE
 WHEN (orders.quantity * price) > 100 THEN (price * .65)
 ELSE (price * .75)
        END
FROM orders 
JOIN inventory
ON orders.product = inventory.product

:

id	customer	quantity	product	price	SALE_PRICE
1	Tia	11	Hanging Files	14.99	9.7435
2	Tia	3	Stapler	7.99	5.9925
3	A+Maintenance	14	Hanging Files	14.99	9.7435
4	Gerald Garner	5	19" LCD Screen	116.9935	134.9925
5	Tia	5	19" LCD Screen	179.99	116.9935
6	Tia	4	HP Printer	89.99	58.4935

With this query, we have now successfully reduced all orders by 25^% and also applied an additional 10^% discount to any order totaling over ^$100.00.
In each of the examples above, SQL CASE has been utilized to perform presentation level adjustments on data values and its versatility provides limitless results.

Posted in: SQL