I'm having problems retrieving the correct data using a WHERE NOT EXISTS clause and wonder if anyone could please help me. The script below is supposed to look for anyone under the age of 25 who doesn't not have a mailing type like '%25%' and then put them into the temporary table TEMPADDU25B.
This works fine if the database record doesn't have ANY mailing types at all. However, if someone doesn't have a mailing type like '%25%' but does have another mailing type of, say, like '%30' then the contact will not be retrieved. I hope this makes sense!
Ideally I want to retrieve ALL contacts that DON'T have a mailingtype like '%25%' whether or not they have any other mailtype on their record.
select distinct contact.serialnumber, contact.dateofbirth, mailingpreference.mailingtype,
GETDATE() AS [todays date], [current age] INTO TEMPADDU25B
left join mailingpreference on contact.serialnumber=mailingpreference.serialnumber
CROSS APPLY(VALUES (DATEDIFF(YY,dateofbirth,GETDATE()) - CASE WHEN DATEADD(YY,DATEDIFF(YY,dateofbirth,GETDATE()),dateofbirth) > GETDATE() THEN 1
END)) A([current age])
where NOT EXISTS (SELECT mailingtype from mailingpreference WHERE mailingtype LIKE '%25%') and [current age] <25 OR
mailingpreference.mailingtype IS NULL and [current age] <25
ORDER BY SERIALNUMBER