Search code examples
sqlsql-serveriif

Is there an easier way than nested iifs to count mismatched conditions in SQL server

The picture below shows a table of accounts and the outcomes that I want to count. Ie every time the account number is 106844 and the outcome is "MESSAGE" or "ESCALATION EMAIL" that should count as 1 where any other outcome counts as 0. What I would normally do is a horrible mess of iifs like

   sum( iif([account] = '106719' and [Outcome] in ('MESSAGE','ESCALATION_EMAIL'),1,iif([account] = '310827' and [outcome] <> 'ABORT' and 'CALL_OUTCOME_LB' in ("Call patched to Customer Care","Message Taken"),1,iif( ... , 0) as [Total Outcomes]

and so on but man it feel like there's got to be an easier way or one less prone to making a random mistake in the 7th nested iif and messing the whole thing up. Any ideas?

input

Solution

  • Don't use iif(). It is a function brought into SQL Server for back-compatibility to MS ACCESS. Why would you want to be backwards compatible to such a thing?

    Use the ANSI standard CASE expression:

      sum(case when account = '106719' and Outcome in ('MESSAGE', 'ESCALATION_EMAIL')
           then 1
           when account = '310827' and outcome <> 'ABORT' and
                'CALL_OUTCOME_LB' in ("Call patched to Customer Care", "Message Taken")
           then 1
           . . .
           else 0
       end) as Total_Outcomes

    I would also advise you to name your columns so they don't need to be escaped (why "Total Outcomes" became "Total_Outcomes"). That simplifies the code.