Post reply

Case in SQL

  • October 28, 2014 at 12:01 pm

    #397573

    Is there a better way to approach this ? Sorry there was an error earlier .

    Case when Server_Excluded <> 'Yes' and Aged30 > 0 Then 'Green'

    when Server_Excluded <> 'Yes' and Group= '' then 'Amber'

    when Server_Excluded <> 'Yes' and ImportantAged30 >0 Then 'Amber'

    End as Status

  • October 28, 2014 at 12:49 pm

    #1755331

    That seems fine to me. An alternative could be:

    Case when Server_Excluded <> 'Yes' and Aged30 > 0 Then 'Green'

    when Server_Excluded <> 'Yes' and (Group= '' OR ImportantAged30 >0) Then 'Amber'

    End as Status

    I can't say that one is better than the other, it's mainly based on your preferences.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • October 29, 2014 at 9:39 am

    #1755564

    I see. Thank you

  • October 29, 2014 at 9:54 am

    #1755573

    What is Server_Excluded = 'Yes'? In other words, don't forget the ELSE.

    Tally Tables - Performance Personified
    String Splitting with True Performance
    Best practices on how to ask questions

  • October 29, 2014 at 9:59 am

    #1755575

    You could also use nested CASE functions to avoid repeating the Server_Excluded test.

    CASE WHEN Server_Excluded <> 'Yes' THEN

    CASE WHEN Aged30 > 0 THEN 'Green'

    WHEN Group = '' OR ImportantAged30 > 0 THEN 'Amber' END

    END

    It's a matter of style preference, I doubt there's any measurable performance difference.

    The main consideration I would use is which version looks easier to understand and maintain.

  • October 29, 2014 at 7:51 pm

    #1755664

    Just to know, how many possible values do you have for the Server_Excluded column? Does it allow NULLs, as well?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

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

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