SQL query using equal or not equal to

seems to me, it look like you are trying to compare multiple rows in "filteredNew_Evidence" table for a single column "new_relatedapplicationid" like multiple columns

instead of the below condition

and (aa.new_evidenceforoutputidname not in ('Pre-Start Assist','Business Starts') )

please try replace with below

NOT EXISTS(select top 1 1  from filteredNew_Evidence aaab 
where aaab.new_relatedapplicationid=aa.new_relatedapplicationid
and aaab.new_evidenceforoutputidname in ('Pre-Start Assist','Business Starts'))