SQL Server - CASE Function- Examples

I heard many people asking about alternative for Oracle DECODE in SQL Server. There is no such function in SQL Server but through CASE function we can construct the same expression.

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

Complicated Script (For an example)

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