Post reply

find only clients with zero transactions

  • July 14, 2011 at 2:07 pm

    #244305

    In my table of clienthistory I want to find only those clients who have zero transactions.

    so client 1234 might have 45 line items and one line has a balance, I do not want this guy, I want client 6789 who has 30 line items but all of the balance boxes are = NULL

    i would put some samples of what I have tried, but honestly I have no idea where to start. the only things I can think of would return nulls by client or non-nulls but not a client with all nulls

  • July 14, 2011 at 2:13 pm

    #1353096

    --no transactions at all for a givien client:

    SELECT * FROM clients

    LEFT OUTER JOIN clienthistory

    ON clients.clientID = clienthistory.clientID

    WHERE clienthistory.clienthistoryID IS NULL

    --the balance of all transactions is zero.

    SELECT * FROM clients

    LEFT OUTER JOIN (SELECT clientID,SUM(transactionamt) AS TheSum FROM clienthistory GROUP BY clientID) clienthistory

    ON clients.clientID = clienthistory.clientID

    WHERE TheSum=0

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • July 14, 2011 at 2:13 pm

    #1353097

    Would be easier with some ddl and sample data. I tossed this together which is really sparse but should show you an example of how to do this.

    create table #test

    (

    ClientID int,

    Balance numeric(9,2)

    )

    insert #test (ClientID, Balance)

    values (1, 23), (1, 34.56), (2, null), (2, 43.5), (3, null), (3, null)

    select *

    from #test

    select ClientID, SUM(balance)

    from #test

    group by ClientID

    having SUM(balance) is null

    That work for you?

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • July 14, 2011 at 2:48 pm

    #1353114

    Lowell, was only working with one table.

    Sean that worked. why the heck didn't I think of summing instead of just looking for nulls....DOH!

    Thanks

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply