Not does not exclude query info

Question

I have a really long query and I'm finding that my NOT is not excluding what's in parenthesis after the NOT.

I saw Exclude and where not exists, but I'd have to re-select for that, and there's too many complicatedly joined tables in what I selected already, plus one table is very big and takes a long time to select what I have already, so I can't re-select because it will make the query take too long. How do I get this exclusion to work?

INSERT INTO #UNeedingC(id, CASEID, firstname, lastname, userid, AGEOFNOTIFICATION, DATETIMEDECISIONMADE, DELEGATESYSTEM, Person_id, request_type_id, service_place_id, status_summary, externalUserId, subject, onDate, externalPersonId, externalSystemId) 
select distinct
 c.id
 ,uc.case_id
 ,t_case.FIRSTNAME as first
 ,t_case.LASTNAME as last
 ,t_case.user_id as userid
 ,CONVERT(VARCHAR, DATEDIFF(dd, SC.status_change_date, GETDATE())) + ' Day(s) ' + CONVERT(VARCHAR, DATEDIFF(hh, SC.status_change_date, GETDATE()) % 24) + ' Hour(s) ' as [AGE OF NOTIFICATION]   
 ,SC.status_change_date AS [DATE TIME DECISION MADE]
 ,[ckoltp_sys].DBO.ckfn_GetStringLocaleValue(152,9,uc.delegate_system,50,0)  AS [DELEGATESYSTEM] 
 ,c.person_id 
 ,uc.request_type_id  ------
 ,uc.service_place_id
 ,uc.status_summary
 ,eou.external_id
 ,c.tzix_id+' '+[ckoltp_sys].dbo.ckfn_GetStringLocaleValue(148,9,uc.status_summary,5,0)+' type' AS subject 
 ,dateadd(  hour,41,dateadd(day,0,datediff(d,0,sc.status_change_date)) )   AS onDate        
 ,emd.externalId externalPersonId 
 ,eou.system_id as externalSystemId
 --,u.disable
from
    #tempC t_case with (NOLOCK) 
    inner join dbo.org_case c with (nolock)  ON t_case.Person_id=c.Person_id
    INNER JOIN dbo.org_2_case uc with (NOLOCK) ON uc.case_id=c.id 
    inner JOIN dbo.ORG_LOS S WITH (NOLOCK) ON S.case_id =  UC.case_id 
    inner JOIN dbo.ORG_EXTENSION SC WITH (NOLOCK) ON SC.los_id= S.id 
    inner join dbo.org_user u with (NOLOCK) on u.id=t_case.user_id
    inner join dbo.org_person op with (NOLOCK) on op.id=c.Person_id
    inner JOIN dbo.u_person_concept_value MC ON MC.CID = op.cid --this is the slow table
    inner join dbo.EXTERNAL_ORG_USER_DATA eou with (NOLOCK) ON eou.org_user_id = t_case.user_id
    inner join dbo.EXTERNAL_person_DATA emd with (NOLOCK) ON emd.CID = op.cid --op.id --?
    WHERE     
        DATEDIFF(day, SC.status_change_date , GETDATE()) <= 2 
        AND
        u.disable <> 1 
        AND
            ( --(denied/approved)
            dbo.ckfn_GetStringLocaleValue(148,9,uc.status_summary,5,0) = 'Denied' 
            OR
            (dbo.ckfn_GetStringLocaleValue(148,9,uc.status_summary,5,0) in( 'Fully Approved', 'Partially Approved')) 
            ) 
                AND
                (
                    (
                      ISNULL(uc.request_type_id,'') in( 12)     
                      AND DATEDIFF(month, SC.status_change_date , GETDATE()) <= 2    
                    )
                    OR 
                    (
                        ISNULL(uc.request_type_id,'') in( 10,11) 
                                
                    )   
                    --OR 
                    --(
                    -- --exclude this
                    --  (
                    --      MC.concept_id = '501620' --general val1 (1000/1001)
                    --      AND  
                    --      (C.ID in (select case_id from #CASES where str_value in ('1000','1001'))    
                    --      AND (uc.service_place_id = 31 OR uc.service_place_id = 32)) 
                    --  )    --not
                    --) --or
                 )--AND
             
            AND
               (t_case.firstname not like '%external%' and t_case.lastname not like '%case manager%')

            AND 
                (
                    C.ID in (select case_id from #CASES where concept_id='501620')--MC.concept_id = '501620'
                ) 
    --overall around AND (denied/approved)--
    and DBO.ckfn_GetStringLocaleValue(152,9,uc.delegate_system,50,0) in ('AP','CA')
    AND NOT --this not is not working...this appears in query results
                    (
                     --exclude these
                        (
                            MC.concept_id = '501620' 
                            AND  
                            (C.ID in (select case_id from #CASES where str_value in ('1000','1001'))
                            AND (uc.service_place_id = 31 OR uc.service_place_id = 32)) 
                        )    --not
                    )   --

select * from #UNeedingC

results show what is excluded:

    id         caseid  firstname   lastname   userid   ageofNotification  Datetimedecisionmade  DelegateSys  Person_id  request_type_id  service_place_id  status_summary  externalUserId  subject           
                    onDate            externalPersonId  externalSystemId  
    000256200  256200  Sree        Par        1234      0                 Apr 5                 CA  
4270000     11              31                3                sparee         000256200 Fully Approved tested Ad   2021-04-06 17:00  363000           2

My question: do you know why the NOT is not working and how I can get this to exclude without another select? See "this not is not working" comment. I searched online but only found exclude and where not exists, which require another select, which I don't want.

You need to thoroughly read [this article](https://stackoverflow.com/help/minimal-reproducible-example) to make your question answerable. Minimal and reproducible is really essential part of question. I mean you can find the rows which you expect to be excluded, create the table with that rows, use them as input to your filter apart of all other filters and after that there may be a starting point for answer. Without all of this actions and actual tables with data it is a waste of time to parse the query manually. — astentx, Apr 05 '21 at 14:41
The concept_id column is not in the output. How are we supposed to know whether it was excluded or not? — Nicholas Hunter, Apr 05 '21 at 15:18
I get extra output, so concept_id is not the issue. concept_id is 501620 for the row returned. The problem is that a row that should be excluded is not. See 31/32 comment line. — Michele, Apr 05 '21 at 15:23

score 0 · Accepted Answer · answered Apr 05 '21 at 16:48

I think I figured it out: "NOT acts on one condition. To negate two or more conditions, repeat the NOT for each condition," from not on two things.

This seems to work:

...
AND             
                     --exclude these
                        (
                            MC.concept_id = '501620' --general val1 (1000/1001)
                            AND  
                            (C.ID not in (select case_id from #CASES where str_value in ('1000','1001'))
                            AND (uc.service_place_id not in ('31','32'))) 
                        )    --not

It is described here: https://learn.microsoft.com/en-us/sql/t-sql/language-elements/operator-precedence-transact-sql?view=sql-server-ver15 — astentx, Apr 06 '21 at 06:43

Not does not exclude query info

1 Answers1