List Transaction that meet Greater Than Criteria

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

List Transaction that meet Greater Than Criteria

Author

Topic

egerencher
Starting Member

3 Posts

Posted - 2012-09-19 : 18:26:55

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 query that can identify each ssn where the sum of any of their transaction amounts > 1000 within a two day period in 2012. If a ssn has transaction amounts > 1000 within a two day period, I need the query to return all the transactions for that ssn.

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--------$10
4-----------98765----04/01/12--------$600
5-----------43210----04/02/12--------$600
6-----------43210----04/03/12--------$100
7-----------13579----04/02/12--------$600
8-----------24568----04/03/12--------$100
Here is an example of the raw data in the Customer Table:

CustKey-----SSN
12345------123456789
67890------123456789
98765------987654321
43210------987654321
13579------246801357
24568------246801357
Here are the results I need:

Trans#------SSN---------Date---------Amount
1--------123456789----01/01/12---------$600
2--------123456789----01/02/12---------$500
3--------123456789----01/03/12----------$10
4--------987654321----04/01/12---------$600
5--------987654321----04/02/12---------$600
6--------987654321----04/03/12---------$100

As you can see in my results included all transactions for SSN 123456789 and 987654321, and excluded SSN 246801357.

Here is what I tried.

select c.ssn, t.*
from TransDtl t
join Customer c on t.CustomerKey = c.CustomerKey
join(select c.ssn, SUM(t.TransAmt) as Total
from TransDtl t
join Customer c on t.CustomerKey = c.CustomerKey
where t.postdate between '1/4/2012' and '1/5/2012'
group by c.ssn
having SUM(t.transamt) > 1000) x
on x.ssn = c.SSN
where t.postdate between '1/4/2012' and '1/5/2012'

The problem I am having is that I can only run my above query for 2 days out of the year. I want to run the query for the whole year, and identify any transaction in that year where the sum of the transactions exceeded 1000 in any given 2-day period; and then list all the transactions for the ssn.

Thank you!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-19 : 18:54:30

If I understood the requirements correctly (i.e., the dates have to be consecutive, and any SSN that satisfies the two-consecutive-day requirement should be listed etc.), see if this query gives you what you are looking for.

This code parses without any errors, but I have not tested the logic. If you post sample data in a form that someone can copy and paste to create the test tables and populate with data, you would get faster and better answers.

;WITH cte AS
(
	SELECT
		t.TransNum,
		c.SSN,
		t.Date,
		t.Amount
	FROM
		TransactionsTable t
		INNER JOIN CustomerTable c ON 
			c.CustKey = t.CustKey
)
SELECT * FROM cte
WHERE SSN IN
(
	SELECT a.SSN
	FROM cte a INNER JOIN cte b
		ON a.SSN = b.SSN
		AND a.Date = DATEADD(dd,1,b.Date)
		AND a.Amount + b.Amount > 1000
)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources