We help IT Professionals succeed at work.

# Exclude a Zero Value

on
Medium Priority
631 Views
I would like to modify a report that calculates residents' outstanding balances.  The below code returns a row (for each resident) if the balance is \$0 or an actual (positive or negative) balance. How do I use a case statement to exclude \$0 balances?

IsNull((Select SUM(tr.sTotalAmount) FROM trans tr
WHERE (tr.hretentionacct in (1,4,5) or
tr.snotes in (':Deposit Credit', ':Security Deposit Credit'))
and tr.itype = 7
and tr.sTotalAmount > 0
and t.hMyPerson = tr.hPerson ),0) -
IsNull((Select SUM(d.samount) from trans tr left outer join detail d on tr.hmy =            d.hchkorchg
WHERE (tr.hretentionacct in (1,4,5) or
tr.snotes in (':Deposit Credit', ':Security Deposit Credit'))
and tr.itype = 7
and tr.sTotalAmount > 0
and t.hMyPerson = tr.hPerson ),0) as 'Current Owed'

Comment
Watch Question

## View Solutions Only

Commented:
Probably not what you want, but to actually remove the rows from the selection I would insert the result of your select into a temporary table or array (depending on size) of resident, balance and return a second select off that.

Anything else is likely to be just as inefficient under the hood.
SQL Server Data Dude
CERTIFIED EXPERT
Most Valuable Expert 2013
Author of the Year 2015
Commented:
>Select SUM(tr.sTotalAmount)
Assuming this is what you mean by 'balance', to do an expression such as <> 0 based on an aggregate add a HAVING block.
Select SUM(tr.sTotalAmount)
FROM trans tr
WHERE blah, blah, blah
HAVING SUM(tr.sTotalAmount) <> 0

If by balance you mean the whole SUM(tr.sTotalAmount)  minus SUM(d.samount), mvdeveloper is correct in that you're probably getting to the point where you'd need a temp table, or if this logic is only used once throw it in a CTE/subquery, and do the  'balance <> 0' in the outer query / main query.
Software Engineer
CERTIFIED EXPERT

Commented:
You could just use a sub query as well

select *
from