Click here to Skip to main content
15,867,453 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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 10:05am
Comments
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?

1 solution

Try this:

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


For further details, please see:
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[^]
 
Share this answer
 
v2
Comments
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?

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900