Post reply

Case Statement Help

  • 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!!

  • September 14, 2010 at 2:07 pm

    #1220957

    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

  • September 14, 2010 at 2:08 pm

    #1220960

    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!

  • September 14, 2010 at 2:20 pm

    #1220975

    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

  • September 14, 2010 at 2:24 pm

    #1220982

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

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

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