x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 235

# SQL - Sum of Cash based on Customer ID

To my dearest experts,

I've searched the KB, but can't locate a basic version of what I'm looking for.

I have a Transaction table (ActivityHist) that show's bank activity.  What I need is (hopefully) a rather simple solution.  I want to see a list of Transactions (TranNo) on a specific Date (BookDate) broken down by Customer (CustID) with a sum of activity (BaseAmt) of 10000.01 or higher.

All data fields are in the same table, no joins and such required.

Any and all help would be greatly appreciated.

-Nick
0
NCollinsBBP
• 2
1 Solution

Microsoft SQL Server Developer, Architect, and AuthorCommented:
``````Declare @dt date = '2013-10-01'

SELECT CustID, TranNo, Sum(BaseAmt) as BaseAmtSum
FROM ActivityHist
GROUP BY CustID, TranNo
HAVING Sum(BaseAmt) > 10000
``````
Q:  Does the transactions make up the sum, where you wish to apply the criteria of 'greater than ten grand'?

btw I wrote an article on SQL Server GROUP BY Solutions that would be a good read.
0

Senior DBACommented:
You will need a JOIN, since you have to determine the total while also listing the details.

Below is as close as I get with the info so far.  I used ABS(BaseAmt) in the SUM() so that credits/debits would not cancel each other out, as either/both are activity on the account.  If that's not correct, naturally remove the ABS() :-) .

DECLARE @BookDate date -- chg data type to to exactly match the data type on the table
DECLARE @trans_total decimal(9, 2) -- chg data type to to exactly match the data type on the table

SET @BookDate = '20131022'
SET @trans_total = 10000.01

SELECT ah.*
FROM dbo.ActivityHist ah
INNER JOIN (
-- check for any customer that meets/exceeds the desired total of value of all trans
SELECT CustID
FROM dbo.ActivityHist
WHERE
BookDate = @BookDate
GROUP BY CustID
HAVING SUM(ABS(BaseAmt)) >= @trans_total
) AS ah_total ON
ah_total.CustID = ah.CustID
WHERE
ah.BookDate = @BookDate
ORDER BY
ah.CustID, ah.TranNo
0

Author Commented:
This was the first solution provided and it worked perfectly. Thank you!

(Sorry for the late reply... had some massive hardware / server issues that bled into multiple days... this became a low priority).

-Nick
0

Microsoft SQL Server Developer, Architect, and AuthorCommented: