Exclude a Zero Value

Posted on 2012-08-13
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
    LVL 5

    Accepted Solution

    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 65

    Assisted Solution

    by:Jim Horn
    >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

    You could just use a sub query as well

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

    Author Closing Comment

    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

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Join & Write a Comment

    This is my first ever Article on EE or elsewhere; therefore, please bear with me if I have some discrepancies in my writing. I read many articles and questions related to "how to pass values to SSIS packages at run-time?"  Hence, this common ques…
    In this article—a derivative of my blog post (—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
    Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

    729 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

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now