SQL Case NOT IN not working
<p>Why does SQL Case 'not in' not work? 'in' works though it does not match for 'not in'.</p>

<p>Or perhaps there is a better way to do it?</p>

<p>I am trying to apply a result to a match that meets on criteria set so long as it does not meet the other criteria set.</p>

<p>Thank you</p>

<p><div class="snippet" data-lang="js" data-hide="false" data-console="true" data-babel="false">
<div class="snippet-code">
<pre class="snippet-code-html lang-html prettyprint-override"><code>---------- Newsletters ----------

CASE WHEN ( C.ID in  (SELECT  [ContactID]  FROM [Customers].[dbo].[uvLSProspectiveContacts] WHERE [EnrolDate] &gt; '2016-12-31 00:00:00.000' AND YearLevel = 0 AND ReferenceTable &lt;&gt; 'Enquiry')
   AND C.ID not in (SELECT  [ContactID]  FROM [Customers].[dbo].[uvLSProspectiveContacts] WHERE [EnrolDate] &gt; '2016-12-31 00:00:00.000' AND YearLevel &gt; 0 AND ReferenceTable &lt;&gt; 'Enquiry')
   )
   OR
   ( C.ID in  (SELECT  [ContactSpouseID]  FROM [Customers].[dbo].[uvLSProspectiveContacts] WHERE [EnrolDate] &gt; '2016-12-31 00:00:00.000' AND YearLevel = 0 AND ReferenceTable &lt;&gt; 'Enquiry')
   AND C.ID not in (SELECT  [ContactSpouseID]  FROM [Customers].[dbo].[uvLSProspectiveContacts] WHERE [EnrolDate] &gt; '2016-12-31 00:00:00.000' AND YearLevel &gt; 0 AND ReferenceTable &lt;&gt; 'Enquiry')
   )
   OR
   ( ESC.ContactEnquiryID in  (SELECT  [ContactID]  FROM [Customers].[dbo].[uvLSProspectiveContacts] WHERE [EnrolDate] &gt; '2016-12-31 00:00:00.000' AND YearLevel = 0 AND ReferenceTable = 'Enquiry')
   AND ESC.ContactEnquiryID not in (SELECT  [ContactID]  FROM [Customers].[dbo].[uvLSProspectiveContacts] WHERE [EnrolDate] &gt; '2016-12-31 00:00:00.000' AND YearLevel &gt; 0 AND ReferenceTable = 'Enquiry')
   )
 THEN 'K' 

 ELSE 

CASE WHEN ( C.ID in  (SELECT  [ContactID]  FROM [Customers].[dbo].[uvLSProspectiveContacts] WHERE [EnrolDate] &gt; '2016-12-31 00:00:00.000' AND YearLevel &gt; 0 AND YearLevel &lt; 7 AND ReferenceTable &lt;&gt; 'Enquiry')
   AND C.ID not in (SELECT  [ContactID]  FROM [Customers].[dbo].[uvLSProspectiveContacts] WHERE [EnrolDate] &gt; '2016-12-31 00:00:00.000' AND YearLevel = 0 AND ReferenceTable &lt;&gt; 'Enquiry')
   AND C.ID not in (SELECT  [ContactID]  FROM [Customers].[dbo].[uvLSProspectiveContacts] WHERE [EnrolDate] &gt; '2016-12-31 00:00:00.000' AND YearLevel &gt; 6 AND ReferenceTable &lt;&gt; 'Enquiry')
   )
   OR
   ( C.ID in  (SELECT  [ContactSpouseID]  FROM [Customers].[dbo].[uvLSProspectiveContacts] WHERE [EnrolDate] &gt; '2016-12-31 00:00:00.000' AND YearLevel &gt; 0 AND YearLevel &lt; 7 AND ReferenceTable &lt;&gt; 'Enquiry')
   AND C.ID not in (SELECT  [ContactSpouseID]  FROM [Customers].[dbo].[uvLSProspectiveContacts] WHERE [EnrolDate] &gt; '2016-12-31 00:00:00.000' AND YearLevel = 0 AND ReferenceTable &lt;&gt; 'Enquiry')
   AND C.ID not in (SELECT  [ContactSpouseID]  FROM [Customers].[dbo].[uvLSProspectiveContacts] WHERE [EnrolDate] &gt; '2016-12-31 00:00:00.000' AND YearLevel &gt; 6 AND ReferenceTable &lt;&gt; 'Enquiry')
   )
   OR
   ( ESC.ContactEnquiryID in  (SELECT  [ContactID]  FROM [Customers].[dbo].[uvLSProspectiveContacts] WHERE [EnrolDate] &gt; '2016-12-31 00:00:00.000' AND YearLevel &gt; 0 AND YearLevel &lt; 7 AND ReferenceTable = 'Enquiry')
   AND ESC.ContactEnquiryID not in (SELECT  [ContactID]  FROM [Customers].[dbo].[uvLSProspectiveContacts] WHERE [EnrolDate] &gt; '2016-12-31 00:00:00.000' AND YearLevel = 0 AND ReferenceTable = 'Enquiry')
   AND ESC.ContactEnquiryID not in (SELECT  [ContactID]  FROM [Customers].[dbo].[uvLSProspectiveContacts] WHERE [EnrolDate] &gt; '2016-12-31 00:00:00.000' AND YearLevel &gt; 6 AND ReferenceTable = 'Enquiry')
   )
   
 THEN '1-6' 

  ELSE 

CASE WHEN ( C.ID in  (SELECT  [ContactID]  FROM [Customers].[dbo].[uvLSProspectiveContacts] WHERE [EnrolDate] &gt; '2016-12-31 00:00:00.000' AND YearLevel &gt; 6 AND YearLevel &lt; 13 AND ReferenceTable &lt;&gt; 'Enquiry')
   AND C.ID not in (SELECT  [ContactID]  FROM [Customers].[dbo].[uvLSProspectiveContacts] WHERE [EnrolDate] &gt; '2016-12-31 00:00:00.000' AND YearLevel &lt; 7 AND ReferenceTable &lt;&gt; 'Enquiry')
   )
   OR
   ( C.ID in  (SELECT  [ContactSpouseID]  FROM [Customers].[dbo].[uvLSProspectiveContacts] WHERE [EnrolDate] &gt; '2016-12-31 00:00:00.000' AND YearLevel &gt; 6 AND YearLevel &lt; 13 AND ReferenceTable &lt;&gt; 'Enquiry')
   AND C.ID not in (SELECT  [ContactSpouseID]  FROM [Customers].[dbo].[uvLSProspectiveContacts] WHERE [EnrolDate] &gt; '2016-12-31 00:00:00.000' AND YearLevel &lt; 7 AND ReferenceTable &lt;&gt; 'Enquiry')
   )
   OR
   ( ESC.ContactEnquiryID in  (SELECT  [ContactSpouseID]  FROM [Customers].[dbo].[uvLSProspectiveContacts] WHERE [EnrolDate] &gt; '2016-12-31 00:00:00.000' AND YearLevel &gt; 6 AND YearLevel &lt; 13 AND ReferenceTable = 'Enquiry')
   AND ESC.ContactEnquiryID not in (SELECT  [ContactSpouseID]  FROM [Customers].[dbo].[uvLSProspectiveContacts] WHERE [EnrolDate] &gt; '2016-12-31 00:00:00.000' AND YearLevel &lt; 7 AND ReferenceTable = 'Enquiry')
   )
 THEN '7-6' 

  ELSE 

CASE WHEN ( C.ID in  (SELECT  [ContactID]  FROM [Customers].[dbo].[uvLSProspectiveContacts] WHERE [EnrolDate] &gt; '2016-12-31 00:00:00.000' AND ReferenceTable &lt;&gt; 'Enquiry')
   )
   OR
   ( C.ID in  (SELECT  [ContactSpouseID]  FROM [Customers].[dbo].[uvLSProspectiveContacts] WHERE [EnrolDate] &gt; '2016-12-31 00:00:00.000' AND ReferenceTable &lt;&gt; 'Enquiry')
   )
   OR
   ( ESC.ContactEnquiryID in  (SELECT  [ContactID]  FROM [Customers].[dbo].[uvLSProspectiveContacts] WHERE [EnrolDate] &gt; '2016-12-31 00:00:00.000' AND ReferenceTable = 'Enquiry')
   )

 THEN 'General'

  ELSE ''  END END

 END 
END AS ProspectiveNews,</code></pre>
</div>
</div>
</p>