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.
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