CASE, WHEN, THEN and ELSE in SQL Server 2022

Core of CASE Statement with its component such as WHEN, THEN & ELSE to create a conditional logic operation in SQL Server

Introduction

Structured Query Language (SQL) is the backbone of managing and manipulating data in relational database systems like SQL Server, mySQL, OracleSQL, PostgreSSQL, IBM DB2, Terada and so on.  To perform conditional operations within SQL queries, we often use the CASE statement along with its companions WHEN, THEN, and ELSE In this blog, we will dive into the usage of these keywords in SQL Server and explore their versatility. Let’s get started

CASE Statement

The CASE statement in SQL Server is a powerful tool for performing conditional logic within a query. It allows you to return different values based on specified conditions. The basic structure of a CASE statement looks like this:

CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
ELSE elseResult
END

• condition1, condition2, etc.: These are the conditions you want to evaluate.
• result1, result2, etc.: The corresponding values or expressions to return if the conditions are met.
• elseResult: The value to return if none of the conditions are met (optional).

For this blog, we are going to look at multiple use cases of the CASE statement in real business situation. For demonstration purpose, we are going to use the transactions table which we fetch all the records using the simple SELECT * FROM transactions as seen below.

In the transactions table, we have the Number of Store column and we want to use the CASE to return Yes for Number of Store that is greater than or equal to 3 and No for less than 3 stores and give 5% to each of the customer operating three or more stores which will be calculated by the Sales Amount. In the third scenario, we want to be fair by giving 5%, 8%, 10% and 15% percent bonus for customers operating 1, 2, 3, and 4 or more stores respectively. The percent bonus will be multiply by the Sales Amount for each customer. The final scenario will be that, in order to discourage the customers from owing us, we have a measure in place to penalty any customer whose Previous Balance is greater than 0 and who do not have credit facility arrangement with us. We will fetch all the columns from the table excel the CustomerID. To achieve that using the CASE started:

• Execute the query below

SELECT
[Customer Name],
[Previous Balance],
[Credit Facility],
[Sales Amount],
[Number of Store],
CASE
WHEN [Number of Store] >= 3 THEN ‘Yes’
ELSE ‘No’
END AS [3 or more stores],
CASE
WHEN [Number of Store] >= 3 THEN [Sales Amount] * 0.10
ELSE 0
END AS Bonus,
CASE
WHEN [Number of Store] = 1 THEN [Sales Amount] * 0.05
WHEN [Number of Store] = 2 THEN [Sales Amount] * 0.08
WHEN [Number of Store] = 3 THEN [Sales Amount] * 0.010
WHEN [Number of Store] >= 4 THEN [Sales Amount] * 0.15
END AS [Fair Bonus],
CASE
WHEN [Previous Balance] >0 AND [Credit Facility]<>’Yes’ THEN [Previous Balance] * 0.10
ELSE 0
END AS Penalty
FROM
transactions

• Click on Execute to run the code

From the screenshot below, for the first scenario, we have Yes for customer with 3 or more stores while No is assigned to customers with less than 3 stores. For the second scenario, we calculated the bonus value for customers operating three or more stores while customers with less than 3 stores received 0. With regards to the third scenario, we gave fair bonus to each of the customers based on the actual number of stores they operate. Finally, customers whose Previous Balance is greater than 0 and who do not have Credit Facility with us received 10% penalty.