Credit cards used at at least 2 merchants from a list

Transact-SQL
1

Hi,

I'm attempting to write a query to pull credit cards that were used at at least two merchants from a list of merchants.

My table has card_number and merchant_name fields. There is row for every card transaction.

Lets say I have merchant_a, merchant_b, merchant_c, and merchant_d. I want to find cards that were used at least two of those merchants. I'm not sure how I would go about finding cards that were used at at least two of those merchants and not end up with cards that were used at say merchant_a twice.

Any help would be greatly appreciated. Hopefully I explained it well enough. If not, I'll be happy to try and clarify.

Thanks,

Josh

2 
SELECT
	cardNumber
FROM
	Tbl
GROUP BY
	cardNumber
HAVING
	COUNT(DISTINCT merchane_name) > 1
3

Thanks.

I need to the merchant name to be one that exists in a list of merchant names though. My table contains tens of thousands of merchant names. I need to look at cards used at at least two merchants from a small list of merchants.

4 
SELECT
    cardNumber
FROM
    Tbl
where mecrchant_name in ('one', 'two', 'three', ... 'last')
GROUP BY
    cardNumber
HAVING
    COUNT(DISTINCT merchane_name) > 1
5

Thanks. Greatly appreciate it.

I was way over thinking this.

6

One more question. In the same table it also has the date of the transaction. What if I wanted to only see those cards where the transactions that took place at two of those merchants are no more than 3 days apart?

7

Add an exists clause like shown below

SELECT  
	cardNumber
FROM
	Tbl t1
WHERE
	mecrchant_name IN ( 'one', 'two', 'three', 'last' )
	AND EXISTS
	(
		SELECT *
		FROM Tbl t2
		WHERE
			t2.mecrchant_name <> t1.mecrchant_name
			AND t2.mecrchant_name IN ( 'one', 'two', 'three', 'last' )
			AND ABS(DATEDIFF(dd,t1.transactionDate,t2.transactionDate)) <= 3
	)
GROUP BY
	cardNumber
HAVING
	COUNT(DISTINCT mecrchant_name) > 1;
8

Thanks so much guys. Works perfect. You helped find a new fraud trend :smile: