SQL Tutorial - CASE Statements

Another video brought to you by BeardedDev, bringing you tutorials on Business Intelligence, SQL Programming and Data Analysis.
In this video I talk about how to use CASE statements in SQL Server.
T-SQL Querying
www.amazon.com/gp/product/073...
T-SQL Fundamentals
www.amazon.com/gp/product/150...
Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions
www.amazon.com/gp/product/073...
CASE Statements were introduced to SQL Server in 2008. They are used to evaluate conditions and return a result based on those conditions. CASE Statements can be used in SELECT, UPDATE, DELETE, WHERE, HAVING.
In this CASE Tutorial we look at a common use of CASE statements within SELECT and also the benefit and common downfalls when writing CASE queries.
CASE Statement Syntax:
CASE WHEN [condition] THEN [result] END
SQL Queries in this video:
SELECT
CustomerId
, FirstName
, MiddleName
, LastName
, CASE
WHEN CustomerId = 5995 THEN 'Unknown'
WHEN Gender = 'M' THEN 'Male'
WHEN Gender = 'F' THEN 'Female'
ELSE 'Unknown'
END AS Gender
, DOB
FROM dbo.CustomersSample
Please feel free to post comments.

[@johnbreslin4521]

Brilliant, this is exactly what I wanted to do. I can never remember the syntax myself.

[@malisengjabum5959]

The way you explain and write the query is Very Good. You are a great teacher. Thanks.

[@stealthassulter]

Excellent JOB buddy. Thank you for your time.

[@doktoren99]

Sick video!

[@mmusciano]

Thank you very much

[@mostinho7]

9:45 done

[@ciruzzi7]

It's hard for me to find videos that are clear and understandable. As a beginner with SQL, this was spot-on. I will definitely look for more videos from you...and btw luv the accent!

[@BeardedDevData]

That’s great, thanks for the feedback, let me know if there are any areas you would like me to do tutorials on.

[@ciruzzi7]

@@BeardedDevData how about SSRS reports?

[@alexkoh4045]

THANK YOU SO MUCH

[@enjaku2600]

Thanks mate. Very clear and useful. Much appreciated. -eric

[@BeardedDevData]

No problem, glad you found the video useful.

[@somalipirates]

AMAZING. thank you.. keep them coming

[@hemantbhatt1486]

Good explanation

[@leticialima7123]

Hi, Can you submit an the test database to we ?

[@harrydadson7467]

This is very good playlist, Thank you very much...only criticism I have is I wished it was is chronological order :)

[@BeardedDevData]

I appreciate that, I do need to sort out the playlists.

[@WizOWash]

Thanks so much for this tutorial! Excellently done. I did have a question about the line you added for the CustomerID when you set the Gender value to Unknown. How does SQL know to set the Gender value to NULL. From the statement WHEN CustomerID = 5995 THEN 'Uknown'. It does not reference the Gender field at all in that line. Thanks

[@BeardedDevData]

I am effectively overwriting the Gender column within the SELECT, as you can see in the CASE statement, “END AS Gender”, I don’t need to actually reference the column to set a value.

[@AZ_German]

Had the same question. Thanks for asking.

[@vru5696]

how to write case statements in where clause to check dates with between logic. for eg. startdate between table1.startdate and table2.enddate

[@BeardedDevData]

You can just use WHERE startdate BETWEEN table1.startdate AND table2.enddate, there is no need for a case statement

[@vru5696]

@@BeardedDevData actually based on nulls record in date need to use case

[@QloweeD]

Hi, can you help me right up a case when statement for the example below,
Say case when count is 4 then a=20 and rest of variables in the group (b,c,d) = 10.
I want the statement to assign 20 to "a" and assign 10 to each of the remaining variable.

[@BeardedDevData]

Can you give me more details, it sounds like you are initially aggregating the values?

[@siddhipore1715]

How to write a case statment to reject those records which has null values in gender column?

[@BeardedDevData]

You wouldn't want to use a case statement in this scenario, instead use WHERE gender IS NOT NULL, this will filter NULLs in the gender column. If you want to change what is returned then use a case statement CASE WHEN gender IS NULL THEN 'Unknown' END, case statements will not apply filtering.

[@siddhipore1715]

@@BeardedDevData thanks for the reply

[@sairam8406]

thumb's up

[@useless3905]

Thanks sir can you do this with if else statement?

[@BeardedDevData]

An IF ELSE statement is used to control logic, you can use an IIF function to replace simple CASE statements, the main difference is that with IIF you can return one of two value but with CASE statements you can return one of many values.

[@azzamf.khayyat8072]

Thanks for making the video. What application do u recommend for writing queries in mobile? --WHERE mobile IN ( 'apple' , 'android' );

[@BeardedDevData]

To be honest I have never tried to run queries on a mobile platform, I can see the benefit from a training point of view but don't think it would be useful in production, I would be curious to know if you find a way to do it though.

[@AlishSafarli]

All the case statements I have seen are about gender column lol set 'm' to 'something'
is there another advanced query you can show ?

[@BeardedDevData]

Sure, you are right the examples are quite straight forward and case statements are quite an interesting topic. I will put some videos up showing applying ranges using multiple conditions, using built in functions inside case statements and building a dynamic where clause with case statements.

[@AlishSafarli]

BeardedDev that would be great bro! What is your programming level?senior?

[@ondaride777]

For genders using multiples instead of just 2 makes it even more clear and politically correct lol

[@Gigatless]

One can only imagine how many sjw were triggered by this tutorial