IIF function - SQL Server 2008 analogue

Question

I have this SQL code written for SQL Server 2012 and working fine there:

select 
    d.sp_id, 
    sum(iif(d.Published is null, 0, 1)) as Published,
    sum(iif(d.Cancelled is null, 0, 1)) as Cancelled,
    sum(iif(d.Revenue is null, 0, d.Revenue)) as Revenue
from
    [MyTable] d
group by 
    d.sp_id

Here Published and Cancelled are datetime values.

The Revenue is a decimal(18,8) value.

I need to migrate it to SQL Server 2008 R2 (where IIF is not available).

What is the best way of doing this? (while preserving its logic at 100%).

I can think of using CASE but this sounds too verbose to me.

Many thanks in advance

@Habib This is what I was thinking of, yes. But as said, sounds somehow too verbose to me. — peter.petrov, Sep 08 '14 at 15:49
You know that `IIF` is just syntactic sugar, it translates into `CASE` — Habib, Sep 08 '14 at 15:49

score 1 · Accepted Answer · edited May 23 '17 at 12:21

1

The IIF function is just syntactic sugar, it translates into a CASE statement.
In versions of SQL Server before 2012, use CASE instead of IIF.

IIF function - SQL Server 2008 analogue

1 Answers1