SQL Server  - Help with case statement

Posted on 2012-09-07
Last Modified: 2012-09-07
I need assistance with the CASE statement in a where clause. I have table of TEMPERATURE I need to convert the following pseudo code into the SQL statement.  
I have a TEMP field - I need to return the 'value' based upon the value.
To return 3 new INT fields   GOOD, ,OK, FAIR -  NOTE:  A VALUE CAN BE BOTH GOOD AND OK.

   if (TEMP >= 68.7 &&TEMP <= 83.30)

       if (TEMP >= 70.16 &&TEMP <= 81.84)
       else if (TEMP > 81.84 && TEMP < 83.30)
Question by:JElster
    LVL 25

    Expert Comment

    by:Lee Savidge
    The logic does make sense because you're trying to return 2 different values for ranges that cross over.
    LVL 69

    Expert Comment

    by:Éric Moreau
    Because of the overlap, if it is GOOD, it is automatically OK (same for FAIR)

    select *
    , case
        when temp between 68.7 and 70.16 then 'GOOD'
        when temp between 70.16 and 81.84 then 'GOOD - OK'
        when temp between 81.84 and 83.30 then 'GOOD - FAIR'
        else 'unknown'
    from temperature
    LVL 1

    Author Comment

    I need it to return more than 1 value if overlapped.. that's the problem.. it can be both


    TEMP        GOOD       OK       FAIR
    71                1             1
    LVL 69

    Accepted Solution

    now it is clearer:

    select TEMP
    , case when temp between 68.7 and 83.30 then 1 else NULL END AS 'GOOD'
    , case when temp between 70.16 and 81.84 then 1 else NULL END as 'OK'
     , case when temp between 81.84 and 83.30 then 1 else null end as 'FAIR'
    from temperature

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Join & Write a Comment

    SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
    Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…

    733 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now