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


Did you like this post?
kick it on DotNetKicks.com
subscribe via rss subscribe via e-mail
print this post follow me on twitter


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

 
  Feedback:

comments

9 Responses to "Using CASE Expression in SQL Server to create a Computed Column"
  1. Shannon Lowder said...
    April 8, 2010 at 6:13 PM

    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

  2. Burt C said...
    April 8, 2010 at 8:21 PM

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

  3. Suprotim Agarwal said...
    April 8, 2010 at 8:25 PM

    Yes you can do that using COALESCE

  4. H Miracle said...
    April 30, 2010 at 1:09 AM

    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

  5. John David said...
    May 2, 2010 at 10:27 PM

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

  6. Shaira Jones said...
    November 5, 2010 at 12:31 AM

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

  7. Sonic Producer said...
    February 17, 2011 at 9:53 PM

    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…

  8. Ardyss Diaz said...
    May 3, 2011 at 10:49 PM

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

  9. Ardyss Body Magic Shapers said...
    September 29, 2011 at 4:36 PM

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

 

Copyright © 2009-2014 All Rights Reserved for SQLServerCurry.com by Suprotim Agarwal | Terms and Conditions