SQL Server, mySQL, OracleSQL, PostgreSSQL, IBM DB2, Terada, and other relational database systems rely on Structured Query Language (SQL) to manage and manipulate data.  We frequently utilize the CASE statement, along with its siblings WHEN, THEN, and ELSE, to perform conditional operations within SQL queries. In this blog, we will look at how to use these keywords in SQL Server and how versatile they are. Let's get this party started.

EXAMPLE OF A CASE STATEMENT
The SQL Server CASE statement is a useful tool for applying conditional logic within a query. It enables you to return different values based on the conditions you specify. A CASE statement has the following fundamental structure:

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).

In this article, we will look at several usage cases of the CASE statement in real-world business situations. For demonstration purposes, we will use the transactions table and retrieve all of the records using the basic SELECT * FROM transactions shown below.

We have the Number of Store column in the transactions table, and we want to use the CASE to return Yes for Number of Stores greater than or equal to 3 and No for less than 3 stores, and we want to give 5% to each customer who operates three or more stores, which will be calculated by the Sales Amount. In the third case, we want to be fair by offering a 5%, 8%, 10%, and 15% incentive to customers who operate one, two, three, or four locations, respectively. For each customer, the percentage bonus will be multiplied by the sales amount. For each customer, the percentage bonus will be multiplied by the sales amount. To deter consumers from owing us, we have a measure in place to penalize any client whose Previous Balance is larger than 0 and who does not have a credit facility arrangement with us. The CustomerID will be extracted from all columns in the Excel table. To accomplish this, the CASE began:

Run 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.

HostForLIFE.eu SQL Server 2022 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.