Case Statement Help

Question

Post reply

Case Statement Help

Tek

SSCommitted

Points: 1697
More actions
September 14, 2010 at 1:39 pm

#387783

I'm trying to write out a single CASE statement, but with multiple WHENS in it. I guess I'm writing out the syntax wrong.
******************************************************
This is what I have so far and it works:
CASE
WHEN Code Between 1 and 24 THEN Hours ELSE NULL
END as HoursBillable
******************************************************
I'm wanting to enter another WHEN statement and looks like this, but does not work.
CASE
WHEN Code Between 1 and 24 THEN Hours ELSE
WHEN Code Between 26 and 29 THEN Hours ELSE NULL
END as HoursBillable
******************************************************
Can someone help point out what I'm doing wrong. Thanks!!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95460 More actions · Answer 1

You can only have one else condition for the case statement, after all of the WHEN conditions.

CASE

WHEN Code Between 1 and 24 THEN Hours

WHEN Code Between 26 and 29 THEN Hours

ELSE NULL

END as HoursBillable

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Lowell SSC Guru Points: 323518 More actions · Answer 2

you error is really minor; a case can only have ONE "ELSE" statement:

CASE

WHEN Code Between 1 and 24

THEN Hours

WHEN Code Between 26 and 29

THEN Hours

ELSE NULL

END as HoursBillable

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

WayneS SSC Guru Points: 95460 More actions · Answer 3

Looks like I got in ahead of Lowell by 1 or 2 ms.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Tek SSCommitted Points: 1697 More actions · Answer 4

SWEET!! Thanks! I knew it had to be something minor.