Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 450
  • Last Modified:

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
       }
   }
0
JElster
Asked:
JElster
  • 2
1 Solution
 
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
 
Éric MoreauSenior .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

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now