SQL Tutorial - Nesting CASE statements

Learn how to nest CASE statements in this SQL tutorial, nesting CASE statements can be used for layers of conditional logic but can become complex and difficult to read so always remember to add comments to your code, I also like to indent my case statements to improve readability. The other issue with nested CASE statements is that they are not very dynamic but we could overcome that problem by storing the values in a separate table then joining to that table.
To follow along with this SQL tutorial run the below code, also includes nested case statements created in the video, replace greater than and less than with actual symbols:
CREATE TABLE dbo.Customers
(
CustomerKey INT IDENTITY(1, 1) NOT NULL
CONSTRAINT PK_Customers_CustomerKey PRIMARY KEY (CustomerKey),
FirstName VARCHAR(50) NULL,
LastName VARCHAR(50) NULL,
BusinessName VARCHAR(50) NULL,
CustomerType CHAR(1)
);
INSERT INTO dbo.Customers (FirstName, LastName, BusinessName, CustomerType)
VALUES
('Albert', 'Gunner', NULL, 'P'),
(NULL, NULL, 'Beach Store', 'B'),
('Catherine', 'Smith', NULL, 'P'),
(NULL, NULL, 'Duncan''s Hair', 'B'),
('Erin', 'Fairclough', NULL, 'P'),
(NULL, NULL, 'Gaming Zone', 'B'),
('Henry', 'Long', NULL, 'P');
CREATE TABLE dbo.Orders
(
OrderKey INT IDENTITY(1, 1) NOT NULL
CONSTRAINT PK_Orders_OrderKey PRIMARY KEY (OrderKey),
CustomerKey INT NULL,
OrderDate DATE NULL,
OrderAmount DECIMAL(8, 2)
);
INSERT INTO dbo.Orders (CustomerKey, OrderDate, OrderAmount)
VALUES
(1, '20220501', 1000.00),
(1, '20220602', 9500.00),
(2, '20220501', 3000.00),
(2, '20220602', 3000.00),
(3, '20220501', 12000.00),
(3, '20220602', 6000.00),
(3, '20220501', 4000.00),
(4, '20220602', 7000.00),
(4, '20220501', 9000.00),
(4, '20220602', 10000.00),
(4, '20220501', 6000.00),
(5, '20220602', 8000.00),
(5, '20220501', 8000.00),
(6, '20220602', 22000.00),
(7, '20220501', 3000.00),
(7, '20220602', 2000.00);
SELECT
*,
CASE
WHEN CustomerType = 'P' THEN
CASE
WHEN NoOfOrders (greater than or equal to) 3 THEN
CASE WHEN TotalAmount (greater than or equal to) 20000.00 THEN 20.00 ELSE 15.00 END
WHEN NoOfOrders (greater than or equal to) 2 THEN
CASE WHEN TotalAmount (greater than or equal to) 10000.00 THEN 18.00 ELSE 12.00 END
ELSE 5.00 END
WHEN CustomerType = 'B' THEN
CASE
WHEN NoOfOrders (greater than or equal to) 3 THEN
CASE WHEN TotalAmount (greater than or equal to) 30000.00 THEN 25.00 ELSE 15.00 END
WHEN NoOfOrders (greater than or equal to) 2 THEN
CASE WHEN TotalAmount (greater than or equal to) 20000.00 THEN 19.00 ELSE 13.00 END
ELSE 6.00 END
END AS Discount
FROM dbo.Customers AS Cust
INNER JOIN
(
SELECT
CustomerKey,
COUNT(OrderKey) AS NoOfOrders,
SUM(OrderAmount) AS TotalAmount
FROM dbo.Orders
GROUP BY
CustomerKey
) AS Ord
ON Cust.CustomerKey = Ord.CustomerKey;

[@BeardedDevData]

Can you think of a more elegant solution?

[@oraclesql]

Well done on hitting 1 million total views Bearded Dev. Your channel is pure gold and I wish you were still putting up content

[@BeardedDevData]

Thanks so much for the support, I will return don't worry, I'm hoping early next year, I'm just trying to get some things in order so I can post regularly.

[@kennisrogers2585]

This was very helpful! Thank you.

[@referralhelper]

Great job with real world problem

[@ramona3075]

Thank you so much bro

[@yaseersayeed1215]

Hi solution and explanation are awesome, need more case with respect to system time concat statement and many more thanks in advance but it's required for further improvement in this case statement.

[@kummithavenkatareddy2302]

Why we need to give columns in Group by clause which are using case statements?
Select
c1,
c2='x' sum(values in c3) sum(values in c4) end as 'z'
from table A
where c5='Y' and c6='w'
group by c1, ( Do we need to use c2 here )
c1, Z column (I need Z values grouped by only c1 column )

[@BeardedDevData]

I think you're asking why we need to use the Customers table, that's because the discount depends on the CustomerType. If you can clarify the question a bit more, I can help.

[@MethodOverRide]

On mobile so I cannot elaborate, but this feels like it could benefit from a stored procedure to calculate the discount. I'm thinking about calling a proc in the case statement and feed it the parameters for the different discounts.
WHEN CustomerType
THEN EXEC dbo.CalculateDiscount(...)
END AS Discount

[@BeardedDevData]

Interesting idea, as it's just a simple select I would suggest a table-valued function and storing the discount values in another table and joining to it.

[@superfreiheit1]

a function is also useable to calculate the discount.

[@uttambongarde6558]

In my case statement they provide the conditions for the columns but we need to fetch the values from that column.
E.g. CASE When ABC.(Tbl n).Contact desc= 'phonenumber'.
I want here phonenumbers for different IDs.
How to do this??

[@BeardedDevData]

There are a couple of solutions I have in mind but it would depend on the setup, if you have a situation where you have contacts and they have different contact numbers but a preferred in another column then it can be as straight forward as CASE prefferedcontactnumber WHEN 'Home' THEN homecontactnumber WHEN 'Mobile' THEN mobilecontactnumber etc. This will return the preferred contact number in a column, the other scenario you might be describing is a generic table where you have an ID, attribute and a value, in that scenario pivoting the data would probably be easier.

[@srisanthoshk1532]

Hi, in all your videos you talk about relational set which is basis for relational databases. So in order completely understand the relational set which math should I learn, so that I completely understand the logic behind any given SQL query? Please advice.

[@BeardedDevData]

Hi Sri, thanks for the comment, it's an interesting question. One thing I would say is that you don't need to fully understand the math behind it, if you have a basic understanding of sets and predicates it will serve you well when writing SQL. However if you would like to look into then the two branches are set theory and predicate logic. Edgar F Codd has written some books on relational databases such as the relational model for database management that you might like to read.

[@NAVINSUTTLE]

good video, but not able to clearly hear.