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

CASE Expression SQL Server

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


About The Author

Suprotim Agarwal, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and a new one recently at The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.

Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook

9 comments:

Shannon Lowder said...

I have to say I'd never thought to combine a CASE statement in a computed column. It's a interesting use!

---
Shannon Lowder
Database Engineer
http://toyboxcreations.net

Burt C said...

Is there a way I can avoid nulls and print 0 instead?

Suprotim Agarwal said...

Yes you can do that using COALESCE

H Miracle said...

hi.., myself is Mark Andrew, i have just started to learn .net before few times, so i am have no idea about this, but after read your post, i feel, it is really informative information. thanks
H Miracle

John David said...

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

Shaira Jones said...

This blog is certainly very helpful and presented some new ideas. Thanks for sharing.

Sonic Producer said...

This site is really interesting. You bring up some great points about your article… .. Thanks for the great information about website.. It is my first time here in this site so job well done…

Ardyss Diaz said...

Thanks for sharing. It's a bit over my head, but trying to learn all the same. Cheers

Ardyss Body Magic Shapers said...

This post is certainly very helpful and presented some new ideas. Thanks for sharing.