CODEWITHSUNDEEP
sql-server-2005if-statementcase-when
Josh McKearin
Josh McKearin

Reputation: 742

Using IF logic in where clause

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

Upvotes: 0

Views: 242

Answers (1)

Josh McKearin
Josh McKearin

Reputation: 742

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.

Upvotes: 1

Related Questions