Case Statement - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Case Statement

Author

Topic

daisyd65
Starting Member

3 Posts

Posted - 2008-07-30 : 11:40:37

Hi,
I have a situation where I'm trying to return only the highest level of testing complexity for a lab that may have multiple levels of complexity. (There are three levels of complexity in order from lowest to highest: waived, moderate, high.) For example, lab 5 may perform waived and moderate testing, in which case I only want to return moderate, since that's the highest level of testing. I'm thinking I can do this with a case statement, it's just that everything I've tried hasn't worked.

I tried giving the complexity levels a numeric value, with 'high' having the highest value. Then, tried to only return the max(complexity), but that didn't work. I still get all levels for all labs. It might not even be my case statement that's the problem.

Here's the query that I have now, that still returns all the levels:

select distinct labid, max(complexity) as complexity
, case when complexity like 'high' then '3'
when complexity like 'moderate' then '2'
when complexity like 'waived' then '1'
end complexity2
from activelabtestsystems alts
group by labid, complexity
order by labid

Does anyone have any thoughts? Thank you!

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-07-30 : 11:45:51

1) No need for DISTINCT -- the GROUP BY handles that. remove DISTINCT.

2) Remove Complexity from your GROUP BY list. You should not be grouping on that column, since that is the column you wish to summarize.

3) MAX() and MIN() using varchar gets the max and min based on alphabetical sorting. If you want to rank things like High, followed by Moderated, followed by Waived, then you can just get the MIN() since High<Moderate<Waived when sorted alphabetically.

4) Don't use LIKE, since you are not using any patterns. Just use =.

So all you need:

select labid, min(complexity) as complexity
from activelabtestsystems alts
group by labid
order by labid

If you want to return 1,2,3, instead of High/Moderate/Waived, then wrap a CASE around your min(complexity) expression:

select labid,
case min(labid.complexity) when 'High' then 3 when 'Moderate' then 2 when 'Waived' then 1 end as complexity
from activelabtestsystems alts
group by labid
order by labid

- Jeff
http://weblogs.sqlteam.com/JeffS

daisyd65
Starting Member

3 Posts

Posted - 2008-07-30 : 11:52:45

Oh, brother, it was that easy! Thank you so much, Jeff!

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-07-30 : 12:17:01

On a side note, this only worked because it just so happened that returning the MIN() of those text descriptions gave you what you needed. If the order was different, then you were on the right track, you just need to do it a little differently:

select labid,
Max(case complexity when 'High' then 3 when 'Moderate' then 2 when 'Waived' then 1 end) as MaxComplexity
from activelabtestsystems
group by labid

- Jeff
http://weblogs.sqlteam.com/JeffS

daisyd65
Starting Member

3 Posts

Posted - 2008-07-30 : 14:03:04

Thanks, again, Jeff.

I'll keep that solution in my notes, as well, since you never know when a similar situation will arise.

You're the best!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources