7

I'm trying to offload some work from the CF server to the SQL Server (2008).

I'm running a query and the statusID value that is returned corresponds to one of 4 colors (Green, Yellow, Orange, and Red).

select id, statusID 
from table

If this is the ideal situation to use a case statement, is this correct? 

select id,  
    case  
        when statusid in (1,20,24)  
            then 'red'
    END as xxxx) as yyyy, *
from TABLE

And if this is correct, what goes into xxxx and yyyy above?

HPWD
  • 2,232
  • 4
  • 31
  • 61
  • I found http://stackoverflow.com/questions/63447/how-do-you-perform-an-if-then-in-an-sql-select but I'm not sure how to translate that example to my scenario. – HPWD Mar 07 '14 at 08:45
  • I would consider returning a CSS class name that is descriptive. Something like `Error,Warning,Caution,OK`. That way if you ever have to change a color, it is via CSS rather than SQL – James A Mohler Mar 07 '14 at 16:29
  • Up-voting just for this: "I'm trying to offload some work from the CF server to the SQL Server (2008)." – Adrian J. Moreno Mar 07 '14 at 17:16

1 Answers1

5

You're close with the syntax, although you'd only want a maximum of one AS to give the column a name, so you could have something like this (of course, I've dreamt up values to illustrate options):

SELECT      id,  
            CASE 
                WHEN statusid IN (1,20,24) THEN 'red'
                WHEN statusid IN (2,30,34) THEN 'yellow'
                WHEN statusid 8 THEN 'orange'
                WHEN statusid > 35 THEN 'green'
                ELSE 'unrecognised'
            END AS ColorName,
            statusid

FROM        dbo.table
Rowland Shaw
  • 37,700
  • 14
  • 97
  • 166
  • Absolutely perfect. Thank you. I'll approve it when SO says enough time has lapsed. – HPWD Mar 07 '14 at 08:54