SQL Server - Help with case statement

Hi..
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)
   {
          GOOD

       if (TEMP >= 70.16 &&TEMP <= 81.84)
       {
         OK
       }
       else if (TEMP > 81.84 && TEMP < 83.30)
       {
         FAIR
       }
   }
LVL 1
JElsterAsked:
Who is Participating?
 
Éric MoreauConnect With a Mentor Senior .Net ConsultantCommented:
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
0
 
Lee SavidgeCommented:
The logic does make sense because you're trying to return 2 different values for ranges that cross over.
0
 
Éric MoreauSenior .Net ConsultantCommented:
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'
end
from temperature
0
 
JElsterAuthor Commented:
I need it to return more than 1 value if overlapped.. that's the problem.. it can be both

71

TEMP        GOOD       OK       FAIR
71                1             1
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.