December 6, 2011 at 3:43 pm
I'm not sure if I'm just up against a wall or what, but I'm having a hard time visualizing how to write this query. I have a table that is such
CREATE TABLE Customers
[PK_Customer] [int] identity(1,1) not null,
[SSN] [nchar] [9] not null,
[ProprietaryID] [nchar] [12] not null
I recently discovered that somehow, the same SSN has been used across many different customers. I also discovered that this has occurred for many different SSN's. I'm trying to write a query to find out which SSN's have been used for more than X number of customers, based off of the ProprietaryID field. I don't really care that much about cases where the same SSN exists only on two or three different ProprietaryID's, because this can and does happen naturally. I'm more concerned about cases where it exists on hundreds of different ProprietaryID's.
Can anyone give me the right kick in the pants?
December 6, 2011 at 3:48 pm
select count(distinct ProprietaryID), SSN
from Customers
group by SSN
having count(distinct ProprietaryID) > <Some Threshold Value>
order by count(distinct ProprietaryID) DESC
Should work, but I haven't loaded data to test.
p.s. a create table script that runs without error would be greatly appreciated next time.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 6, 2011 at 3:50 pm
Thanks...sorry, I just eyeballed the script.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply