tsql - SQL Calculation IIF returning multiple values

Question

I have an SQL query that is miscalculating results. I am using a nested IFF expression and although the values for both the first and second IIF are returning the correct count, the final 'False' count is counting all values queried.

For example lets say the data set contains 100 values. Condition.1 count = 5, condition.2 = 20, therefore final 'false' value should be 75, however it is returning 100.

Iif
    (
        (
        [Expiry_Date] <= Today() 
        And [Expiry_Date] Is Not Null) 
        Or ([Expiry_Date] Is Null 
        And [Is_Mandatory] = 'true'
        ) 
        And [Comments] Not Like '%Not applicable%' 
        And [Comments] Not Like '%Competency does not expire%' 
        And [Comments] Not Like '%Not Applicable%' 
        And [Comments] Not Like '%Positive notice received%' 
        And [Comments] Not Like '%Employee does not transport clients%' 
        And [Comments] Not Like '%Does not expire%' 
        And [Comments] Not Like '%does not expire%', 
        'Non-Compliant (urgent)', 
            Iif
            (
                ([Expiry_Date] Is Null 
                And [Is_Mandatory] = 'true' 
                And [Comments] Like '%Positive notice received%') 
                Or ([Expiry_Date] Is Null 
                And [Is_Mandatory] = 'false' 
                And [Comments] Not Like '%Does not expire%' 
                And [Comments] Not Like '%does not expire%' 
                And [Comments] Not Like '%Not applicable%' 
                And [Comments] Not Like '%Not Applicable%' 
                And [Comments] Not Like '%Employee does not transport clients%'),
                'Non-Compliant',
                'Compliant'
            )

question from:https://stackoverflow.com/questions/65916925/sql-calculation-iif-returning-multiple-values

Answer 1

Waitting for answers