CODEWITHSUNDEEP
t-sql
Diane Madamx
Diane Madamx

Reputation: 1

Trying to convert a "not in" statement to a "not exists" -- I've looked at related questions but still can't get it to work

I've got a simple query my ex-boss left me with. It's using a not in which takes an ENORMOUS amount of time (and for reasons yet to be determined have caused a failover on a server only running one database). I've tried to change it to a not exists and I'm getting 0 records returned. I realize it has to be something simple but there is absolutely no one here who has a clue what I'm talking about.

Here's the query:

declare @Callback table (ProspectRelationCode varchar(1), CallbackAvail int)
insert into @Callback

select
opp.ppc as ProspectRelationCode,
sum(case when dl.Category = 'Refusal' 
    then 1 else 0
end) as CallbackAvail

from CallResult cr
join Opportunity opp
on opp.id = cr.ID
join dbo.DispositionLookup dl
on dl.lookupkey = cr.CallStatus
where dl.Category = 'Refusal' and opp.Assignment not in (
select Assignment from CallResult cr 
join Opportunity opp
on opp.id = cr.ID
where cr.CallStatus in ('VVOL','RVOL')  )
and  opp.listid >= 1400
group by opp.ppc

Here is my (slight) rework:

select
    opp.ppc as ProspectRelationCode,
    sum(case when dl.Category = 'Refusal' 
        then 1 else 0
    end) as CallbackAvail

from CallResult cr
join Opportunity opp
    on opp.id = cr.ID
join dbo.DispositionLookup dl
    on dl.lookupkey = cr.CallStatus
where dl.Category = 'Refusal' and not exists  ( select opp.assignment
from  CallResult cr join Opportunity on cr.id = opp.id 
where cr.CallStatus in ('VVOL','RVOL')  )
and  opp.listid >= 1400
group by opp.ppc

This is probably a piece of cake for most but I'm hitting a brick wall here. I'd appreciate any help whatsoever (and I'm prepared for any yelling at me also).

Thank you! Diane

Upvotes: 0

Views: 30

Answers (2)

Stephan
Stephan

Reputation: 6018

There's need for the subquery because it joins the data in the same way as your first join statement. Since it was data NOT IN subquery. I move your subquery's WHERE clause and negate it like so:

DECLARE @Callback TABLE (
    ProspectRelationCode VARCHAR(1)
    ,CallbackAvail INT
    )

INSERT INTO @Callback
SELECT opp.ppc AS ProspectRelationCode
    ,sum(CASE 
            WHEN dl.Category = 'Refusal'
                THEN 1
            ELSE 0
            END) AS CallbackAvail
FROM CallResult cr
INNER JOIN Opportunity opp ON opp.id = cr.ID
INNER JOIN dbo.DispositionLookup dl ON dl.lookupkey = cr.CallStatus
WHERE   dl.Category = 'Refusal'
        AND cr.CallStatus != 'VVOL'
        AND cr.CallStatus != 'RVOL'
        AND opp.listid >= 1400
GROUP BY opp.ppc

Upvotes: 1

bf2020
bf2020

Reputation: 732

Does this work?

select
    opp.ppc as ProspectRelationCode,
    sum(case when dl.Category = 'Refusal' 
        then 1 else 0
    end) as CallbackAvail

from CallResult cr
join Opportunity opp
    on opp.id = cr.ID
join dbo.DispositionLookup dl
    on dl.lookupkey = cr.CallStatus
where dl.Category = 'Refusal' and not exists  ( select Opportunity.id
from  CallResult cri join Opportunity on cri.id = opp.id 
where cri.CallStatus in ('VVOL','RVOL')  )
and  opp.listid >= 1400
group by opp.ppc

Changed the select list in the sub-query, and also an alias for clarity.

Upvotes: 0

Related Questions