Learn how to a build a cloud-first strategyRegister Now


Exclude a Zero Value

Posted on 2012-08-13
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!
Question by:melikins

Accepted Solution

mvdeveloper earned 1000 total points
ID: 38288455
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.
LVL 66

Assisted Solution

by:Jim Horn
Jim Horn earned 1000 total points
ID: 38288571
>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.
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 38288860
You could just use a sub query as well

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

Author Closing Comment

ID: 38292337
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.

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question