Using CASE Expression in SQL Server to create a Computed Column

Here’s a practical example of using CASE expression in SQL Server. I am creating a computed column using CASE expression where I will automatically calculate the value of a column based on the value of other columns:

The psuedocode is as follows:

If the Quantity of items ordered is in between 10 and 20, give a 10% discount

If the Quantity of items ordered is in between 20 and 30, give a 20% discount

If the Quantity of items ordered is greater than 30, give a 40% discount

Here’s the query using a CASE expression:

DECLARE @tmp TABLE(Quantity integer,
Price decimal,
DiscountedPrice AS CAST (
    CASE
        WHEN Quantity BETWEEN 10 and 20 THEN Price - Price * .10
        WHEN Quantity BETWEEN 20 and 30  THEN Price - Price * .20
        WHEN Quantity >=31 THEN Price -  Price * .40
    END AS Money)
)
  
  
INSERT INTO @tmp values(4, 4000)
INSERT INTO @tmp values(16, 3500)
INSERT INTO @tmp values(12, 5000)
INSERT INTO @tmp values(22, 6620)
INSERT INTO @tmp values(5, 6400)
INSERT INTO @tmp values(21, 1000)
INSERT INTO @tmp values(37, 7000)

SELECT * FROM @tmp

OUTPUT

[Update] : If you want to avoid nulls, check this updated post Using CASE Expression in SQL Server to create a Computed Column – Avoiding Nulls