Using Case label in where clause?

I have several case statements and I would like to use them in a where clause - if possible.

For example:
...
Case
When assigned_to_group in ('accounting', 'marketing', 'Finance') then 'Admin'
When assigned_to_group in ('Maintenance', 'Electrical', 'Plumbing') then 'Support'
When assigned_to_group in ('SysAd', 'Networking', 'HelpDesk') then 'Department'
end 'Tower'
....
Case
when priority = 0 and Hours_To_Resolve <= 336 then 'True'
when priority = 1 and Hours_To_Resolve <= 72 then 'True'
when priority = 2 and Hours_To_Resolve <= 4 then 'True'
when priority = 0 and Hours_To_Resolve <= 1 then 'True'
else 'False'
end 'Met_Time_to_Resolve'
....


I want to formulate my where clause like:

WHERE case_type = 1 AND tower = 'Admin' AND Met_Time_to_Resolve = 'False'

Possible?

When I use the above I get an error that says
Invalid column name 'tower'
Invalid column name 'Met_Time_to_Resolve'
edrz01Asked:
Who is Participating?
 
Kyle AbrahamsConnect With a Mentor Senior .Net DeveloperCommented:
You can't use the new column names, unfortunately you have to recopy the case:

AND tower = 'Admin'
becomes
AND  assigned_to_group in ('accounting', 'marketing', 'Finance')


 Met_Time_to_Resolve = 'False'

becomes


case
when priority = 0 and Hours_To_Resolve <= 336  then 1 else 0
when priority = 1 and Hours_To_Resolve <= 72 then 1 else 0
when priority = 2 and Hours_To_Resolve <= 4 then 1 else 0
when priority = 3 and Hours_To_Resolve <= 1 then 1 else 0   -- you had priorty = 0 twice.  Did you mean priorty = 3?  
END = 0
0
 
wshark83Connect With a Mentor Commented:
the only way to do that is to create the columns first in a temporary table and then use it...otherwise you will have to copy your entire case statement in the where clause
0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
Was editing my comment to say you could use a temporary table, but you take a performance hit depending on the number of rows . . . however shark beat me to it!
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
edrz01Author Commented:
Thanks all, can we re-invent SQL???? It seems like the past 2 hurdles I have posted can't be done. Sigh.... Back to the drawing board.
0
 
jogosCommented:
In the where there may only be 1 ELSE, so corrected the example by: ged325
WHERE 
case
when priority = 0 and Hours_To_Resolve <= 336  then 1 
when priority = 1 and Hours_To_Resolve <= 72 then 1 
when priority = 2 and Hours_To_Resolve <= 4 then 1 
when priority = 3 and Hours_To_Resolve <= 1 then 1 
else 0   -- you had priorty = 0 twice.  Did you mean priorty = 3?  
END = 0

Open in new window


For the temporary table .... that is work (IO) that for every execution must be done.
Repeating a case-condition is only typing during development and performance gain with each execution.
0
 
edrz01Author Commented:
Wow ged325, that worked great!!!! Thank you.

So now my challenge is to calculate a percentage based on the number of false records returned divded by total number of records (true or false).

Can you assist (if so should I open another question)?
0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
another question would do.  Try it out, post where you're having issues.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.