Solved

SQL - Sum of Cash based on Customer ID

Posted on 2013-10-23
4
175 Views
Last Modified: 2013-10-31
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
Comment
Question by:NCollinsBBP
  • 2
4 Comments
 
LVL 65

Accepted Solution

by:
Jim Horn earned 500 total points
ID: 39594036
<air code>  Let's start with this..
Declare @dt date = '2013-10-01'

SELECT CustID, TranNo, Sum(BaseAmt) as BaseAmtSum
FROM ActivityHist
GROUP BY CustID, TranNo
HAVING Sum(BaseAmt) > 10000

Open in new window

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
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 39594582
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 Closing Comment

by:NCollinsBBP
ID: 39614183
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
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39614218
Thanks for the grade.  Good luck with your project.  -Jim
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

758 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

19 Experts available now in Live!

Get 1:1 Help Now