Menu
I have a T-SQL query where I want all negative quantities to be zeroed out.
SELECT
p.productnumber,
v.[Description],
SUM(i.Quantity) as quantity
FROM ...
LEFT JOIN ...
LEFT JOIN ...
LEFT JOIN ...
GROUP BY productnumber, [Description]
Basically if the sum is made up of 5, 5, -1, the result should be 5+5+0=10, and not (5+5+(-1)=9.
How would I do that?
718
use a second case statement: select name, (sum(case when TransTypeName like 'credit%' then (case when amount>0 then amount else 0 end;) else 0 end) - sum(case when TransTypeName like 'Debit%' then (case when amount>0 then amount else 0 end;) else 0 end)) *5/100 as Interest from ...........
The + (Positive) and - (Negative) operators can be used on any expression of any one of the data types of the numeric data type category. The ~ (Bitwise NOT) operator can be used only on expressions of any one of the data types of the integer data type category.
The SQL NOT condition (sometimes called the NOT Operator) is used to negate a condition in the WHERE clause of a SELECT, INSERT, UPDATE, or DELETE statement.
To compute the absolute value of a number, use the ABS() function. This function takes a number as an argument and returns its value without the minus sign if there is one. The returned value will always be non-negative – zero for argument 0, positive for any other argument.
You could use a CASE statement
SUM(CASE WHEN i.Quantity < 0 THEN 0 ELSE i.Quantity END)
Or the proprietary IIF version
IIF(i.Quantity < 0, 0, i.Quantity)
Or a more obscure version
SUM(NULLIF(i.Quantity, -ABS(i.Quantity)))
or just exclude these rows altogether in the WHERE clause if they are not needed for any other purpose.
In Azure SQL Database you can now also use the GREATEST function for this
GREATEST(i.Quantity,0)
just filter out the ones you don't want...
WHERE quantity > 0
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
