Is this a good scenario for MSSQL CASE statement? [duplicate]

Question

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?

Answer 1

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