I've been working on this problem for a long time, and I can't seem to get the query to return the correct results. Below is my problem:
I'm working on a system where users are able to create applications. Each year, they create a new application. I'm looking to find any different users that share the same social security number (SSN). My issue is: since there are multiple applications for each user, using COUNTS will return duplicate SSN even though it is for the same user. Here is an example of the data that I have: (note: none of these are true SSN, I'll use a 6 digit number for an example)
ApplicationNumber User SSN 1000 John 658-895 1001 John 658-895 1002 Kara 112-568 1003 Sarah 598-789 1004 Steve 658-895 1005 Sarah 598-789
I've tried tons of different T-SQL, below is an example:
SELECT User, SSN FROM dbo.Application GROUP BY User, SSN HAVING COUNT(SSN) > 1
This type of query will return something similar to:
User SSN John 658-895 John 658-895 Steve 658-895 Sarah 598-789 Sarah 598-789
Whereas all I want are situations where there is COUNT of more than 1 SSN and where the Users are different:
User SSN John 658-895 Steve 658-895
I would like all of the other records to be hidden, even if there are technically more than 1 (due to one user having 2+ applications which will have the same SSN).
This is a somewhat simplified version of my problem (more tables, slightly more complex logic), but if anyone can point me in the correct direction or give me example queries I would be greatly appreciative. Feel free to ask additional questions if you have them!
(using SQL 2014)