15,563,053 members
See more:
I have some code in a SQL stored procedure which changes negative values in a column to zero

```-Sum(Bonus) As Bonus,
Case WHEN -Sum(Bonus) < 0 THEN 0 ELSE-Sum(Bonus) END As ThresholdBonus,
```

This produces two columns that look like this:

Bonus
-2.45
16.08
-0.69

Threshold Bonus
0
16.08
0

However this is not what I want to happen. I want the columns to show 0 only if the total of the columns is less than 0 but otherwise to show the negative values. Is there a way I can do this in SQL?

What I have tried:

I've only tried what i've done above really but this is not my desired result.

If you're curious the Bonus calculation looks like this:

```SUM(ISNULL(vBookingSessionEmployees.MinWageEarning, 0))
- SUM(vBookingSessionEmployees.TotalBeforeMinWage) AS Bonus```
Posted
Updated 12-Apr-22 11:05am
CHill60 12-Apr-22 13:53pm
There probably is but you are not explaining the requirement particularly well. Are you saying that if the total of the bonus column across all rows is negative then set all negative values to 0 otherwise leave them as they are?

Solution 1

Try this:

SQL
```SELECT SUM(COALESCE(BSE.MinWageEarning, BSE.TotalBeforeMinWage)) - SUM(BSE.TotalBeforeMinWage) AS Bonus
FROM vBookingSessionEmployees AS BSE
...```

COALESCE (Transact-SQL) - SQL Server | Microsoft Docs[^]
ISNULL (Transact-SQL) - SQL Server | Microsoft Docs[^]

Note: use aliases[^]!

Will Sewell wrote:
Thank you for the answer but this seems to return the same result as the code I was using.

You're wrong!
If you don't believe me, check this out:

SQL
```CREATE TABLE SomeData
(
A DECIMAL(8,2),
B DECIMAL(8,2)
);

INSERT INTO SomeData(A, B)
VALUES(NULL, 2.45),
(24.08, 8.00),
(NULL, 0.69);

-- 1)
SELECT COALESCE(A, B) - B AS Total
FROM SomeData;

-- 2)
SELECT ISNULL(A, 0) - B AS Total
FROM SomeData;```

Results:
```#1)
Total
0.00
16.08
0.00

#2)
-2.45
16.08
-0.69```

SQL Server 2019 | db<>fiddle[^]

v2
Will Sewell 13-Apr-22 4:44am
Thank you for the answer but this seems to return the same result as the code I was using.
Maciej Los 13-Apr-22 8:26am
Are you sure?