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;

свернуть