Using IF logic in where clause

Question

Ok, I have done plenty of research on this topic before asking the question, but to avail considering my specific situation. Currently my code looks like this:

SELECT RTRIM(logtype)+'-'+RTRIM(servicenbr)+'-'+CONVERT(varchar(25), idserv) as CaseNbr, (CASE WHEN tn.TeamText IS NULL THEN 'HELPDESK' WHEN tn.TeamText like '' THEN 'HELPDESK' ELSE tn.TeamText END) as SourceTeam, (tn2.TeamText) as DestinationTeam
 FROM teamnames as tn2
    LEFT OUTER JOIN caseaudit AS ca
    ON tn2.teamID = ca.referteamID2
    LEFT OUTER JOIN openstatus As os
    ON ca.logno = os.logno
    LEFT OUTER JOIN teamnames as tn
    ON ca.referteamid1 = tn.teamid
 WHERE CONVERT(smalldatetime,ca.dModLast,101) BETWEEN '2012-06-04' AND '2012-06-11'

 --NEED TO PUT IF CLAUSE HERE

 ORDER BY DestinationTeam

Specifically, my IF clause, or CASE WHEN, needs to test to see

WHERE ca.asggrp1 <> ca.asggrp2 
AND ca.referteamid1 <> ca.referteamid2 AND tn2.isactive = 1 AND tn2.groupid = 18. 

If they are equal, then the ticket should be ignored UNLESS 
ca.asggrp1 = 'CLIENT' AND ca.asggrp2 = 'CLIENT' 
AND ca.referteamid1 <> ca.referteamid2 AND tn2.isactive = 1 AND tn2.groupid = 18.

EDIT:

Alright... let's try this. The query should always pull cases with the limitation:

WHERE ca.referteamid1 <> ca.referteamid2 AND tn2.isactive = 1 AND tn2.groupid = 18

Also, I want to limit the results to when ca.asggrp1 <> ca.asggrp2, except for when they are both equal to 'CLIENT'

EDIT:

One more try here... I lost my audience. This is the best explanation I can give of what I am trying to do.

SELECT RTRIM(logtype)+'-'+RTRIM(servicenbr)+'-'+CONVERT(varchar(25), idserv) as CaseNbr, (CASE WHEN tn.TeamText IS NULL THEN 'HELPDESK' WHEN tn.TeamText like '' THEN 'HELPDESK' ELSE tn.TeamText END) as SourceTeam, (tn2.TeamText) as DestinationTeam
 FROM teamnames as tn2
    LEFT OUTER JOIN caseaudit AS ca
    ON tn2.teamID = ca.referteamID2
    LEFT OUTER JOIN openstatus As os
    ON ca.logno = os.logno
    LEFT OUTER JOIN teamnames as tn
    ON ca.referteamid1 = tn.teamid
 WHERE CONVERT(smalldatetime,ca.dModLast,101) BETWEEN '2012-06-04' AND '2012-06-11'
 AND ca.referteamid1 <> ca.referteamid2 AND tn2.isactive = 1 AND tn2.groupid = 18
----AND (ca.asggrp1 <> ca.asggrp2 UNLESS ca.asggrp1 = 'CLIENT' AND ca.asggrp2 = 'CLIENT')
 ORDER BY DestinationTeam

If, unless... can you please show sample data and desired results for each case? I know your word problem makes sense to you, but it can be quite difficult for others to work backwards from it... — Aaron Bertrand
– Aaron Bertrand, Commented Jun 12, 2012 at 1:31

Josh McKearin · Accepted Answer · 2012-06-12 02:09:21Z

1

I feel like an idiot. I just needed to use a simple OR.

AND (ca.asggrp1 <> ca.asggrp2 OR ca.asggrp1 = 'CLIENT' AND ca.asggrp2 = 'CLIENT')

I guess we all over-complicate things now and again.

edited Jun 12, 2012 at 2:09

answered Jun 12, 2012 at 2:03

Josh McKearin

7524 gold badges19 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Using IF logic in where clause

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related