We help IT Professionals succeed at work.

Exclude a Zero Value

melikins
melikins asked
on
Medium Priority
631 Views
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!
Comment
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
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.
Ephraim WangoyaSoftware Engineer
CERTIFIED EXPERT

Commented:
You could just use a sub query as well

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

Author

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.

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