Sum negative values in Money column

Question

I'm trying to sum two columns, and subtract the two sums to get a balance amount of two columns. But I'm getting the same output for both queries even though I know there are negative values. I need to exclude those negative values in the query.

SELECT SUM(ReceiptAmt) - SUM(BalanceAmt) FROM Receipt 
SELECT SUM(ReceiptAmt) - SUM(BalanceAmt) FROM Receipt WHERE ReceiptAmt > 0.00

Any ideas on how to exclude the negative values? Or even a different approach to this.

Answer 1

Should the BalanceAmt be in the where clause also?

WHERE ReceiptAmt > 0.00 
      AND BalanceAmt > 0.00

Answer 2

this may be what you're after:

SELECT SUM(case when ReceiptAmt<0 then 0 else ReceiptAmt end) - SUM(case when BalanceAmt<0 then 0 else BalanceAmt end) FROM Receipt