SQL Server computed columns (0 if value is negative)

Question

I have a computed SQL Server column that is calculated like:

Number1 - Number2

If it so happens that this math is equal to a negative number I'd like to have a 0 as the value in the field rather than a negative number

IE: 5-10 = -5 (Display 0) or only positive numbers.

SELECT 
    CASE 
       WHEN [Scheduled] - [Purchased] < 0 
          THEN 0 
          ELSE [Scheduled] - [Purchased] 
    END

SELECT 
    IIF([Scheduled] - [Purchased] < 0, 0, [Scheduled] - [Purchased])

Answer 1

Use CASE for this:

SELECT CASE WHEN Scheduled - Purchased < 0 THEN 0 ELSE Scheduled - Purchased END

If using as a computed column simply write:

CASE WHEN Scheduled - Purchased < 0 THEN 0 ELSE Scheduled - Purchased END

SQL Server 2012 onwards, you can also use the IIF function:

SELECT IIF(Scheduled - Purchased < 0, 0, Scheduled - Purchased)