CODEWITHSUNDEEP
databasesql-server-2016calculated-columns
UserSN
UserSN

Reputation: 1013

SQL Server computed columns (0 if value is negative)

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.

enter image description here

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

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

Upvotes: 0

Views: 2638

Answers (2)

chamzz.dot
chamzz.dot

Reputation: 775

You have to use sql query with if else statements here try with following statem,ents or refere this article 

IF value < 0 PRINT '0'  
ELSE PRINT 'value' ;

Upvotes: -1

Mitch Wheat
Mitch Wheat

Reputation: 300599

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)

Upvotes: 3

Related Questions