Exclude a Zero Value

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 tr.uPostDate < (DATEADD(mm,1,('08/01/2012')))
                  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 tr.uPostDate < (DATEADD(mm,1,('08/01/2012')))
                  and d.cashpost < (DATEADD(mm,1,('08/01/2012')))
                  and t.hMyPerson = tr.hPerson ),0) as 'Current Owed'

Thanks in advance for your time!
melikinsAsked:
Who is Participating?
 
mvdeveloperCommented:
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.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>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.
0
 
Ephraim WangoyaCommented:
You could just use a sub query as well

select *
from
  ( Your select statement
  ) A
where A.[Current Owed] <> 0
0
 
melikinsAuthor Commented:
The logic is used several times (in the select) to age the residents' balances so the temp table worked!  Thanks for pointing me in the right direction.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.