asked on

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

ASKER CERTIFIED SOLUTION

Jim Horn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Scott Pletcher

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

NCollinsBBP

ASKER

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

Jim Horn

Thanks for the grade. Good luck with your project. -Jim