Introduction
CASE Expressions in SQL Server are used for the column values substitution to present the result sets in a particular fashion or simple queries. Use cases for such commands are various.
For instance, there is a column containing the department code, but you wish to display the department’s name rather than the code. You could achieve it by doing a JOIN with another table containing the department details. However, let’s assume you want to keep the query relatively simple. Another use case would be returning specific values for the computed values set. Computed columns would not fit if the sets of conditions to specify are not the same.
Typically, the SQL Server CASE Expressions has the form shown in Listing 1.
-- Listing 1: CASE Expression Syntax
-- Simple CASE Expression
SELECT
col1
, col2
, CASE col3
WHEN 'a' THEN 'xxx'
WHEN 'b' THEN 'yyy'
WHEN 'c' THEN 'zzz'
ELSE 'Invalid Value'
END AS col3_name
FROM table_name;
-- Searched CASE Expression
SELECT
col1
, col2
, CASE
WHEN col3 = 1 THEN 'xxx'
WHEN col3 BETWEEN 2 and 9 THEN 'yyy'
WHEN col3 > 10 THEN 'zzz'
ELSE 'Invalid Value'
END AS col3_name
FROM table_name;Simple Case and Searched Case
The two scenarios described above fit quite nicely into the two types of CASE expressions available in SQL Server. A simple CASE Expression allows only equality checks. A Searched CASE expression allows even Boolean expressions.
Note that the results of a CASE expression fit into a single column. Also, observe that we specify the column name just after the CASE clause in the Simple CASE expression. However, in the Searched CASE Expression, we must specify the column name for each Boolean expression. Let’s explore some examples.
Scenario Environment
In our exploration of the CASE expression, we shall use the well-known WideWorldImporters sample database. There, we’ll use the Sales.CustomerTransactions table to demonstrate various scenarios of the CASE expression application. As is common with T-SQL, it is possible to get similar results using other techniques, such as JOINs, but we focus on one table to show the CASE expression capabilities.
Note that one must understand the data handled to use CASE expressions. E.g., we must know what each customer code means to represent the data with more sense to the end-user. In our cases, we can get the information from other tables.
Listing 2 is a simple query showing what the data in the table looks like. Figure 1 shows us an output portion.
-- Listing 2: Data Set in Sales.CustomerTransactions
SELECT TOP (1000) [CustomerTransactionID]
, [CustomerID]
, [TransactionTypeID]
, [InvoiceID]
, [PaymentMethodID]
, [TransactionDate]
, [AmountExcludingTax]
, [TaxAmount]
, [TransactionAmount]
, [OutstandingBalance]
, [FinalizationDate]
, [IsFinalized]
, [LastEditedBy]
, [LastEditedWhen]
FROM [WideWorldImporters].[Sales].[CustomerTransactions] ;
Returning Customer Names Based on Customer ID
In this example, we want to display the name of each customer based on the customer code. We get customers’ names from another table using a JOIN query. Again, we are using the CASE Expression to demonstrate what this approach can do.
-- Listing 3a: Determine Names Using a Join
select distinct top 10 b.CustomerID, a.CustomerName
from Sales.Customers a,Sales.CustomerTransactions b
where a.CustomerID = b.CustomerID;-- Listing 3b: Determine Names Using a Join (Alternative)
select distinct top 10 b.CustomerID, a.CustomerName
from Sales.Customers a
inner join Sales.CustomerTransactions b
on a.CustomerID = b.CustomerID;
Having this information, we write a simple CASE query to retrieve data from Sales.CustomerTransactions alone (See Listing 4). Figure 3 highlights the names returned by the query.
Observe the occurrence of ‘Unknown Customers’ in the output. In the real sense, these customers are not unknown. We do not have them because we did not cater to their CustomerID in our CASE expression. This buttresses the need to understand the data when using CASE Expressions.
-- Listing 4: Simple CASE Expression for Customer Name
SELECT TOP (20)
CASE CustomerID
WHEN 1 THEN 'Tailspin Toys'
WHEN 401 THEN 'Wingtip Toys'
WHEN 801 THEN 'Eric Torres'
WHEN 802 THEN 'Cosmina Vlad'
WHEN 803 THEN 'Bala Dixit'
WHEN 804 THEN 'Alekxandrs Reikstins'
WHEN 805 THEN 'Ratan Podder'
WHEN 806 THEN 'Shi Tu'
WHEN 807 THEN 'Gunnar Lohmus'
WHEN 808 THEN 'Jackson Kolios'
ELSE 'Unknown Customer'
END CustomerName
, [InvoiceID]
, [TransactionDate]
, [TransactionAmount]
, [OutstandingBalance]
, [IsFinalized]
, [FinalizationDate]
, [LastEditedBy]
, [LastEditedWhen]
FROM [WideWorldImporters].[Sales].[CustomerTransactions];
Returning the Customer Class Based on the Transaction Amount
In this example, we use the Searched CASE expression to show which of our customers is of more value regarding the transaction value.
We classify the customers into three groups – Regular, Silver, Gold, and Platinum, based on the transaction value. Of course, this is simplistic. In a real-world scenario, we would need to sum their transactions over a given period. In this case, we are using only a data subset to show the CASE Expression capabilities.
-- Listing 5: Searched Case Expression for Customer Class
SELECT TOP (20)
CASE CustomerID
WHEN 1 THEN 'Tailspin Toys'
WHEN 401 THEN 'Wingtip Toys'
WHEN 801 THEN 'Eric Torres'
WHEN 802 THEN 'Cosmina Vlad'
WHEN 803 THEN 'Bala Dixit'
WHEN 804 THEN 'Alekxandrs Reikstins'
WHEN 805 THEN 'Ratan Podder'
WHEN 806 THEN 'Shi Tu'
WHEN 807 THEN 'Gunnar Lohmus'
WHEN 808 THEN 'Jackson Kolios'
ELSE 'Unknown Customer'
END CustomerName
, [InvoiceID]
, [TransactionDate]
, CASE
WHEN [TransactionAmount] BETWEEN 100 AND 1000 THEN 'Silver Customer'
WHEN [TransactionAmount] BETWEEN 1000 AND 3000 THEN 'Gold Customer'
WHEN [TransactionAmount] >= 3000 THEN 'Platinum Customer'
ELSE 'Regular Customer'
END AS CustomerClass
, [OutstandingBalance]
, [IsFinalized]
, [FinalizationDate]
, [LastEditedBy]
, [LastEditedWhen]
FROM [WideWorldImporters].[Sales].[CustomerTransactions];
Returning the Day of Week Using Nested CASE Expressions
We proceed with the samples by adding a sample telling us what day of the week the Transaction Date was (See Listing 6). Note that we could have achieved this using a much simpler form of the query using the DATENAME function rather than the DATEPART function.
-- Listing 6: Case Expression for Day of Week Using A Function
SELECT TOP (20)
CASE CustomerID
WHEN 1 THEN 'Tailspin Toys'
WHEN 401 THEN 'Wingtip Toys'
WHEN 801 THEN 'Eric Torres'
WHEN 802 THEN 'Cosmina Vlad'
WHEN 803 THEN 'Bala Dixit'
WHEN 804 THEN 'Alekxandrs Reikstins'
WHEN 805 THEN 'Ratan Podder'
WHEN 806 THEN 'Shi Tu'
WHEN 807 THEN 'Gunnar Lohmus'
WHEN 808 THEN 'Jackson Kolios'
ELSE 'Unknown Customer'
END CustomerName
, [InvoiceID]
, CASE
WHEN DATEPART(WEEKDAY,[TransactionDate]) = 1 THEN 'Sunday'
WHEN DATEPART(WEEKDAY,[TransactionDate]) = 2 THEN 'Monday'
WHEN DATEPART(WEEKDAY,[TransactionDate]) = 3 THEN 'Tuesday'
WHEN DATEPART(WEEKDAY,[TransactionDate]) = 4 THEN 'Wednesday'
WHEN DATEPART(WEEKDAY,[TransactionDate]) = 5 THEN 'Thursday'
WHEN DATEPART(WEEKDAY,[TransactionDate]) = 6 THEN 'Friday'
WHEN DATEPART(WEEKDAY,[TransactionDate]) = 7 THEN 'Saturday'
END AS [Day of Week]
, CASE
WHEN [TransactionAmount] BETWEEN 100 AND 1000 THEN 'Silver Customer'
WHEN [TransactionAmount] BETWEEN 1000 AND 3000 THEN 'Gold Customer'
WHEN [TransactionAmount] >= 3000 THEN 'Platinum Customer'
ELSE 'Regular Customer'
END AS CustomerClass
, [OutstandingBalance]
, [IsFinalized]
, [FinalizationDate]
, [LastEditedBy]
, [LastEditedWhen]
FROM [WideWorldImporters].[Sales].[CustomerTransactions];
Labeling Transactions Based on Date
Using the code in Listings 7 and 8, we can label transactions based on the difference between the current date and the transaction date. It also applies to the difference between the transaction date and another column. Therefore, we can introduce columns other than those we are working with as an input to a Boolean expression.
-- Listing 7: Case Expression for Transaction by Comparing Two “Columns”
SELECT TOP (20)
CASE CustomerID
WHEN 1 THEN 'Tailspin Toys'
WHEN 401 THEN 'Wingtip Toys'
WHEN 801 THEN 'Eric Torres'
WHEN 802 THEN 'Cosmina Vlad'
WHEN 803 THEN 'Bala Dixit'
WHEN 804 THEN 'Alekxandrs Reikstins'
WHEN 805 THEN 'Ratan Podder'
WHEN 806 THEN 'Shi Tu'
WHEN 807 THEN 'Gunnar Lohmus'
WHEN 808 THEN 'Jackson Kolios'
ELSE 'Unknown Customer'
END CustomerName
, [InvoiceID]
, CASE
WHEN DATEDIFF(DAY,[TransactionDate],GETDATE()) < 30 THEN 'Current Transaction'
WHEN DATEDIFF(DAY,[TransactionDate],GETDATE()) BETWEEN 30 AND 90 THEN 'Old Transaction'
WHEN DATEDIFF(DAY,[TransactionDate],GETDATE()) BETWEEN 90 AND 365 THEN 'Stale Transaction'
WHEN DATEDIFF(DAY,[TransactionDate],GETDATE()) >= 365 THEN 'Archived Transaction'
END AS [Transaction Age]
, CASE
WHEN [TransactionAmount] BETWEEN 100 AND 1000 THEN 'Silver Customer'
WHEN [TransactionAmount] BETWEEN 1000 AND 3000 THEN 'Gold Customer'
WHEN [TransactionAmount] >= 3000 THEN 'Platinum Customer'
ELSE 'Regular Customer'
END AS CustomerClass
, [OutstandingBalance]
, [IsFinalized]
, [FinalizationDate]
, [LastEditedBy]
, [LastEditedWhen]
FROM [WideWorldImporters].[Sales].[CustomerTransactions];-- Listing 8: Case Expression for Transaction by Comparing Two Columns
SELECT TOP (20)
CASE CustomerID
WHEN 1 THEN 'Tailspin Toys'
WHEN 401 THEN 'Wingtip Toys'
WHEN 801 THEN 'Eric Torres'
WHEN 802 THEN 'Cosmina Vlad'
WHEN 803 THEN 'Bala Dixit'
WHEN 804 THEN 'Alekxandrs Reikstins'
WHEN 805 THEN 'Ratan Podder'
WHEN 806 THEN 'Shi Tu'
WHEN 807 THEN 'Gunnar Lohmus'
WHEN 808 THEN 'Jackson Kolios'
ELSE 'Unknown Customer'
END CustomerName
, [InvoiceID]
, CASE
WHEN DATEDIFF(DAY,[TransactionDate],[FinalizationDate]) < 30 THEN 'Prompt Transaction'
WHEN DATEDIFF(DAY,[TransactionDate],[FinalizationDate]) BETWEEN 30 AND 90 THEN 'Delayed Transaction'
WHEN DATEDIFF(DAY,[TransactionDate],[FinalizationDate]) BETWEEN 90 AND 365 THEN 'Serverely Delayed Transaction'
WHEN DATEDIFF(DAY,[TransactionDate],[FinalizationDate]) >= 365 THEN 'Orphaned Transaction'
END AS [Transaction Response]
, CASE
WHEN [TransactionAmount] BETWEEN 100 AND 1000 THEN 'Silver Customer'
WHEN [TransactionAmount] BETWEEN 1000 AND 3000 THEN 'Gold Customer'
WHEN [TransactionAmount] >= 3000 THEN 'Platinum Customer'
ELSE 'Regular Customer'
END AS CustomerClass
, [OutstandingBalance]
, [IsFinalized]
, [FinalizationDate]
, [LastEditedBy]
, [LastEditedWhen]
FROM [WideWorldImporters].[Sales].[CustomerTransactions];CASE Expressions Outside the SELECT List
We can also use CASE expressions in SET statements, UPDATE statements, WHERE clauses, HAVING Clauses, and ORDER BY Clauses.
The Update statement in Listing 9 updates the OutstandingBalance column of rows with four different customer IDs having different values. This statement is equivalent to writing five different update statements for each CASE and then the ELSE.
-- Listing 9: Update Statement with CASE Expression
UPDATE Sales.CustomerTransactions
SET OutstandingBalance =
(CASE
WHEN CustomerID = 832 THEN 100.00
WHEN CustomerID = 803 THEN 150.00
WHEN CustomerID = 905 THEN 200.00
WHEN CustomerID = 976 THEN 70.00
ELSE 50.00
END
);
SELECT TOP 20 * FROM Sales.CustomerTransactions;Conclusion
SQL and T-SQL allow you to substitute values stored in a column with your desired values. In this article, we have explored Simple and Searched CASE expressions with examples.
CASE Expressions can be used on SELECT clauses, SET statements, UPDATE statements, WHERE, HAVING, and ORDER BY clauses.
