SQL Server – CASE Function- Examples

This article talks about an equivalent of Oracle SQL’s DECODE function in SQL Server. There is no such function in SQL Server but through CASE function we can construct the same expression. (Update: IIF can be used as an alternative since SQL Server 2012 onwards)

CASE function evaluates a list of conditions and returns one of multiple possible result expressions. It has two formats:

(1) Simple Case Function
(2) Searched Case Function

The simple CASE function compares an expression to a set of simple expressions to determine the result. The searched CASE function evaluates a set of Boolean expressions to determine the result. Both formats support an optional ELSE argument.

Refer to the following examples:

Method 1 – Simple Case Function
CREATE TABLE Customers(
CustomerID INT,
CustomerName VARCHAR(30),
ActiveStatus BIT
)

INSERT INTO Customers VALUES(100, ‘John’, 0)
INSERT INTO Customers VALUES(200, ‘Kate’, 1)
INSERT INTO Customers VALUES(300, ‘Julia’, 1)
INSERT INTO Customers VALUES(400, ‘Maddy’, 0)

SELECT CustomerID,
CustomerName,
ActiveStatus = CASE ActiveStatus
WHEN 1 THEN ‘Active’
ELSE ‘Inactive’
END
FROM Customers

Method 2 – Searched Case Function
Case Function With Expressions

CREATE TABLE StudentMarks(
StudentID INT,
StudentName VARCHAR(30),
Subject1 INT,
Subject2 INT,
Subject3 INT,
)

INSERT INTO StudentMarks VALUES(10, ‘John’, 90, 86, 79)
INSERT INTO StudentMarks VALUES(20, ‘Kate’, 69, 58, 43)
INSERT INTO StudentMarks VALUES(30, ‘Julia’, 77, 76, 87)
INSERT INTO StudentMarks VALUES(40, ‘Maddy’, 92, 91, 90)

SELECT * FROM StudentMarks

SELECT StudentID,
StudentName,
Subject1,
Subject2,
Subject3,
SUM(Subject1+Subject2+Subject3) TotalMarks,
Grade = CASE
WHEN SUM(Subject1+Subject2+Subject3)>250 THEN ‘A’
WHEN SUM(Subject1+Subject2+Subject3) BETWEEN 200 AND 250 THEN ‘B’
ELSE ‘C’
END
FROM StudentMarks
GROUP BY StudentID, StudentName, Subject1, Subject2, Subject3

Another example with more conditions/filters:
SELECT
COUNT(CASE WHEN (
                                 ActiveStatus = 1
                                 AND
                                 DATEDIFF(Day, CONVERT(DATETIME, DateColumn1, Getdate()) >= 1
                                 AND
                                 DATEDIFF(Day, CONVERT(DATETIME, DateColumn1, Getdate()) <= 3 )
                                 THEN 1 ELSE 0 END)
                        AS Column1,
COUNT(CASE WHEN (
                                 ActiveStatus = 2
                                 AND
                                 DATEDIFF(Day, CONVERT(DATETIME, DateColumn1, Getdate()) >= 4
                                 AND
                                 DATEDIFF(Day, CONVERT(DATETIME, DateColumn1, Getdate()) <= 6 )
                                 THEN 1 ELSE 0 END)
                          AS Column2,
COUNT(CASE WHEN (
                                ActiveStatus = 3
                                AND
                                DATEDIFF(Day, CONVERT(DATETIME, DateColumn1, Getdate()) >= 7
                                AND
                                DATEDIFF(Day, CONVERT(DATETIME, DateColumn1, Getdate()) <= 9 )
                                THEN 1 ELSE 0 END)
                            AS Column3,
FROM Table1
INNER JOIN Tabl2 On Table1.Column9 = Table2.Column9
INNER JOIN Tabl3 On Table3.Column5 = Table2.Column6