Grouping Same ID's with different Names

Question

Post reply

Grouping Same ID's with different Names

ssc_san

SSCommitted

Points: 1573

December 12, 2022 at 2:13 pm

#4124620

Hello All,

I have a issue where I am looking to get a list of customers with a different names or date of births with a same ID.

Please find some sample data. There are 2 records (A123450, X111111) are the correct and the others need to be retuned in the list.

create table #tst
(
ID VARCHAR(10),
FNAME VARCHAR(10),
LNAME VARCHAR(10),
DOB INT,
GNDR VARCHAR(1),
INSERT_DT DATE
)

INSERT INTO #tst
SELECT 'A123456', 'John', 'Doe', '19700101', 'M', GETDATE()-100
UNION ALL
SELECT 'A123456', 'Jane', 'Max', '19651201', 'F', GETDATE()-50
UNION ALL
SELECT 'A123456', 'Jane', 'Max', '19651201', 'F', GETDATE()-150
UNION ALL
SELECT 'A123456', 'Jane', 'Max', '19651201', 'F', GETDATE()-20
UNION ALL
SELECT 'A123450', 'Mad', 'Max', '19651231', 'M', GETDATE()-5
UNION ALL
SELECT 'A123450', 'Mad', 'Max', '19651231', 'M', GETDATE()-200
UNION ALL
SELECT 'A123450', 'Mad', 'Max', '19651231', 'M', GETDATE()-2
UNION ALL
SELECT 'A023450', 'Forrest', 'Gump', '1991231', 'M', GETDATE()-55
UNION ALL
SELECT 'A023450', 'Forrest', 'Hanks', '1991231', 'M', GETDATE()-180
UNION ALL
SELECT 'A023450', 'Forrest', 'Gump', '1991231', 'M', GETDATE()-31
UNION ALL
SELECT 'B123456', 'Jane', 'Doe', '19700101', 'F', GETDATE()-121
UNION ALL
SELECT 'B123456', 'Jane', 'Doe', '19651201', 'F', GETDATE()-81
UNION ALL
SELECT 'B123456', 'Jane', 'Doe', '19651201', 'F', GETDATE()-3
UNION ALL
SELECT 'X111111', 'Tom', 'Cr', '19660601', 'M', GETDATE()-58
UNION ALL
SELECT 'X111111', 'Tom', 'Cr', '19660601', 'M', GETDATE()-178
UNION ALL
SELECT 'X111111', 'Tom', 'Cr', '19660601', 'M', GETDATE()-8
UNION ALL
SELECT 'Z898989', 'Will', 'Adam', '19800412', 'M', GETDATE()-62
UNION ALL
SELECT 'Z898989', 'Will', 'Roth', '19800412', 'M', GETDATE()-45
UNION ALL
SELECT 'Z898989', 'Will', 'Adam', '19800412', 'M', GETDATE()-80

Thank you for your time and help.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

ratbak Ten Centuries Points: 1358 More actions · Answer 1

SELECT * FROM #tst
WHERE EXISTS (SELECT * FROM #tst dupe
              WHERE dupe.ID = #tst.ID 
              AND (dupe.FNAME <> #tst.FNAME OR
                   dupe.LNAME <> #tst.LNAME OR 
                   dupe.DOB <> #tst.DOB)
             );

ssc_san SSCommitted Points: 1573 More actions · Answer 2

Thank you ratbak! Is there anyway to just get the records that are different. I tries Count window function but it is removing some of the records.

Thank you for your time.

ScottPletcher SSC Guru Points: 101093 More actions · Answer 3

SELECT ID
FROM #tst
GROUP BY ID
HAVING 
    MIN(FNAME) <> MAX(FNAME) OR
    MIN(LNAME) <> MAX(LNAME) OR
    MIN(DOB) <> MAX(DOB)

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

ScottPletcher SSC Guru Points: 101093 More actions · Answer 4

If you want the details too, you can either:

(1) join back to the main table to list all rows <or>

(2) list the min, max and count

--(1)
SELECT t1.*
FROM #tst t1
INNER JOIN (
    SELECT ID
    FROM #tst
    GROUP BY ID
    HAVING 
        MIN(FNAME) <> MAX(FNAME) OR
        MIN(LNAME) <> MAX(LNAME) OR
        MIN(DOB) <> MAX(DOB)
) AS t2 ON t2.ID = t1.ID
ORDER BY t1.ID, t1.FNAME, t1.LNAME, t1.DOB

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

ssc_san SSCommitted Points: 1573 More actions · Answer 5

ssc_san

SSCommitted

Points: 1573

December 12, 2022 at 4:24 pm

#4124650

Thank you for your time Scott! I will try this.

ScottPletcher SSC Guru Points: 101093 More actions · Answer 6

Glad it helped! Here's the 2nd version I mentioned: if you don't have more than 2 total dups, this might be easier to use.

--(2)
SELECT ID, 
    MIN(FNAME) AS FNAME_MIN, MAX(FNAME) AS FNAME_MAX, MIN(LNAME) AS LNAME_MIN, MAX(LNAME) AS LNAME_MAX, MIN(DOB) AS DOB_MIN, MAX(DOB) AS DOB_MAX,
    COUNT(*) AS dup_count,
    STUFF(CASE WHEN MIN(FNAME) <> MAX(FNAME) THEN ', FNAME' ELSE '' END + CASE WHEN MIN(LNAME) <> MAX(LNAME) THEN ', LNAME' ELSE '' END + 
          CASE WHEN MIN(DOB) <> MAX(DOB) THEN ', DOB' ELSE '' END, 1, 2, '') AS columns_that_are_diff
FROM #tst
GROUP BY ID
HAVING 
    MIN(FNAME) <> MAX(FNAME) OR
    MIN(LNAME) <> MAX(LNAME) OR
    MIN(DOB) <> MAX(DOB)

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

ssc_san SSCommitted Points: 1573 More actions · Answer 7

ssc_san

SSCommitted

Points: 1573

December 12, 2022 at 8:21 pm

#4124679

Thank you Scott! This is very helpful.