WITH CTE AS (
       SELECT COUNT (DISTINCT WN.APPKEY) AS appkeycnt  FROM   DEV.DBO.VW_NUMBER WN 
       WHERE WN.ANUMBER  IN ('GNW0060077','0456618')       
         AND  WN.UNITKEY IN (SELECT IC.UNITKEY FROM DEV.DBO.VW_DATA IC WHERE IC.UNITNAME = 'MAS') 
       GROUP BY  WN.DEPTKEY, WN.ANUMBER
    ), CTE2 AS (
       SELECT MAX(appkeycnt) AS maxcnt FROM CTE
    )
    SELECT CASE WHEN maxcnt IS NULL THEN NULL WHEN maxcnt = 1 THEN 1 ELSE 0 END as COUNT
    FROM CTE2
    
 --Working scenario 1 - I have provided two valid ANUMBERS ('GNW0060077','0456618')  and the count result is 1 which is working as expected.    
 --Working scenario 2 - I have provided two conflicting ANUMBERS ('NGL0300008', '235493')  and the count result is 0 which is working as expected. when all the ANUMBERS provided in the query are conflicting then the expected result should be 0. 
 --Working scenario 3 - I have provided two ANUMBERS ('UY1233', 'VAMPQ34') which DOES NOT exist in the database and the result is NULL which is working as expected. 
 --Not working scenario 4 - I have provided all the above ANUMBERS ('GNW0060077','0456618','NGL0300008','235493','UY1233','VAMPQ34') (i.e. combination of valid, conflicting, not exists) the expected result is 1 because one of the ANUMBER is conflicting but it is returning 0.      
 --Not working scenario 5 - I have provided ANUMBERS ('GNW0060077', 'UY1233', 'NGL0300008')  (i.e. combination of one valid, one conflicting, not exists) the expected result is 1 because one of the ANUMBER is conflicting but it is returning 0.  
   
   **Note: other below scenarios are not working**
    
    for the combination of  valid and conflicting ANUMBERS,  the expected result is 1.
    for the combination of  valid and not exists ANUMBERS,  the expected result is 1.
    for the combination of  conflicting and not exists ANUMBERS,  the expected result is NULL.