CODEWITHSUNDEEP
sqlsql-server
Jacques
Jacques

Reputation: 1709

MSSQL: When result of calculation is negative, return 0

I have 2 colums:

I want to get a zero when SELECT (WarehouseStock - WarehouseReserved) as Warehousetotal;

Is there a function or something that will convert negative numbers into 0?

Upvotes: 0

Views: 586

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270061

The case expression is what you use if you want your intent to be obvious. If you want inscrutable code, you could use:

select (WarehouseStock - WarehouseReserved) * (sign(WarehouseStock - WarehouseReserved) + 1) / 2

Upvotes: 1

rumata28
rumata28

Reputation: 395

One solution is to use "CASE" statement:

SELECT
   (CASE
      WHEN WarehouseStock - WarehouseReserved < 0 THEN 0
      ELSE WarehouseStock - WarehouseReserved 
    END) as Warehousetotal
FROM [your_table]

Another could be using VALUE and MAX functions:

SELECT 
  (SELECT MAX(v) 
   FROM (VALUES (0), (WarehouseStock - WarehouseReserved)) AS VALUE(v)) as Warehousetotal
FROM [your_table]

Upvotes: 3

Related Questions