Listing Transactions that Exceed Sum Threshold

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Listing Transactions that Exceed Sum Threshold

Author

Topic

egerencher
Starting Member

3 Posts

Posted - 2012-09-17 : 17:16:49

I need to create a report where I can identify each credit card account and the corresponding transaction where the sum is greater than $1000 within a two day period in 2012.

Here is an example of the raw data in the Customer Table:

Trans#-----Acct#-----Date--------Amount
1--------12345----01/01/12--------$600
2--------12345----01/02/12--------$500
3--------12345----01/03/12--------$700
4--------12345----04/01/12--------$600
5--------12345----04/02/12--------$600

Here are the results I need:

Trans#---Acct#-----Date--------Amount---Group Key
1-------12345----01/01/12--------$600--------1
2-------12345----01/02/12--------$500--------1
2-------12345----01/02/12--------$500--------2
3-------12345----01/03/12--------$700--------2
4-------12345----04/01/12--------$600--------3
5-------12345----04/02/12--------$600--------3

As you can see in my results, Trans# 2 is listed twice as it was part of a group of transaction that exceeded $1000 in 2 days. Each group of transaction is identified by a group key.

Please help!

Erick Gerencher

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-17 : 17:30:22

[code]
SELECT t.*
FROM table t
OUTER APPLY (SELECT 0 AS NextAmt,SUM(Amount) AS prevAmt
FROM table
WHERE [trans#] <= t.[trans#]
AND Date >= DATEADD(dd,DATEDIFF(dd,0,t.Date),-1)
UNION ALL
SELECT SUM(Amount) AS NextAmt,0
FROM table
WHERE [trans#] >= t.[trans#]
AND Date <= DATEADD(dd,DATEDIFF(dd,0,t.Date),1)
)t2
WHERE prevAmt >=1000
OR NextAmt>=1000
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

egerencher
Starting Member

3 Posts

Posted - 2012-09-18 : 18:52:01

Let me attempt to rephrase my question.

I work for a credit card company. Our database has a customer table and a transaction table. Fields in the customer table are SSN and CustomerKey. Fields in the transaction table are CustomerKey, transaction date (Transdate), and transaction amount (TransAmt).

I need a report that can list each transaction for each ssn where the sum of the transaction amounts > 1000 within a two day period in 2012.

Here is an example of the raw data in the Transaction Table:

Trans#-----CustKey-----Date--------Amount

1-----------12345----01/01/12--------$600

2-----------12345----01/02/12--------$500

3-----------67890----01/03/12--------$700

4-----------12345----04/01/12--------$600

5-----------67890----04/02/12--------$600

Here is an example of the raw data in the Customer Table:

CustKey-----SSN

12345------123456789

67890------123456789

Here are the results I need:

Trans#------SSN---------Date---------Amount---Group Key

1--------123456789----01/01/12---------$600--------1

2--------123456789----01/02/12---------$500--------1

2--------123456789----01/02/12---------$500--------2

3--------123456789----01/03/12---------$700--------2

4--------123456789----04/01/12---------$600--------3

5--------123456789-----04/02/12--------$600--------3

As you can see in my results, Trans# 2 is listed twice as it was part of a group of transaction that exceeded $1000 in 2 days. Each group of transaction that exceeded 1000 is identified by a group key.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources