April 08, 2010

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, ASP.NET Architecture MVP works as an Architect Consultant and provides consultancy on how to design and develop Web applications.

Suprotim is also the founder and primary contributor to DevCurry, DotNetCurry and SQLServerCurry. He has also written an EBook 51 Recipes using jQuery with ASP.NET Controls.

Follow him on twitter @suprotimagarwal

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.