We help IT Professionals succeed at work.

Exclude a Zero Value

melikins asked
Medium Priority
Last Modified: 2012-08-14
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!
Watch Question

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.
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015
>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.
Ephraim WangoyaSoftware Engineer

You could just use a sub query as well

select *
  ( Your select statement
  ) A
where A.[Current Owed] <> 0


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.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.