Luis Cazares (4/3/2014)
You don't need several CASE, one is enough.
Case when Status = 'Yes' then 'WithinStatus'
when Hours< Benchmark Then 'OutsideStatus'
Else 'WithinStatus'
End as StatusType.
If a condition evaluates to true, it won't evaluate the following possibilities.
Luis is absolutely correct here. He made the same oversight that you did originally though. Your original post (although over complicated) would work. The last condition in both of our code should be 'InsideStatus'
Case when Status = 'Yes' then 'WithinStatus'
when Hours< Benchmark Then 'OutsideStatus'
Else 'InsideStatus'
End as StatusType
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/