less duplication - SQL Server Forums

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

SQL Server 2000 Forums

Transact-SQL (2000)

less duplication

Author

Topic

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2004-01-09 : 09:39:05

I have a table of patients containing UniqueID, FirstName, LastName, DateOfBirth, PostCode,...

I can now identify the duplicate patients based purely on the fact that the firstname, lastname, date of birth and post code all match (thanks nr). What I need to do now is having some way of assigning the lowest of the uniqueID's to each of the duplicates while keeping the original Unique ID

For example I may have

ID, Firstname, lastname, dob, post code
10 Joe bloggs 1/2/03 ab1 2cd
12 Fred bloggs 2/2/02 cd2 ab1
21 Joe bloggs 1/2/03 ab1 2cd
234 Joan Bloggs 3/2/03 ab1 2cd
245 Joe bloggs 1/2/03 ab1 2cd

and what I need is:-

ID, Old ID, ...
10 10
12 12
10 21
234 234
10 245

OR

ID, Old ID
10 21
10 245

which might be easier, but what I need is for any duplicate to identify the lowest ID from the duplicates. I need this so that I can eliminate the duplicates and match the lab samples of the duplicated patients to the lowest ID'd of the duplicates which is where the results come into it.

Hope I've explained this clearly

steve

Steve no function beer well without

X002548
Not Just a Number

15586 Posts

Posted - 2004-01-09 : 10:23:48

[code]
USE Northwind
GO

CREATE TABLE myTable99 (
[ID] int
, FirstName varchar(20)
, LastName varchar(35)
, DOB datetime
, [Post Code] char(7)
)
GO

INSERT INTO myTable99 (ID, Firstname, lastname, dob, [post code])
SELECT 10, 'Joe', 'bloggs', '1/2/03', 'ab1 2cd' UNION ALL
SELECT 12, 'Fred', 'bloggs', '2/2/02', 'cd2 ab1' UNION ALL
SELECT 21, 'Joe', 'bloggs', '1/2/03', 'ab1 2cd' UNION ALL
SELECT 234, 'Joan', 'Bloggs', '3/2/03', 'ab1 2cd' UNION ALL
SELECT 245, 'Joe', 'bloggs', '1/2/03', 'ab1 2cd'
GO

SELECT a.[ID], a.FirstName, a.LastName, a.DOB, a.[Post Code] INTO myNewTable99 FROM myTable99 a
INNER JOIN (
SELECT FirstName, LastName, DOB, [Post Code], MIN ([ID]) AS MIN_ID
FROM myTable99
GROUP BY FirstName, LastName, DOB, [Post Code]
) AS b
ON a.[ID] = b.MIN_ID

SELECT * FROM myNewTable99
GO

SELECT b.[ID], a.[ID] AS Old_ID
FROM myTable99 a
INNER JOIN myNewTable99 b
ON a.FirstName = b.FirstName
AND a.LastName = b.LastName
AND a.DOB = b.DOB
AND a.[Post Code] = b.[Post Code]
WHERE a.[ID] <> b.[ID]
UNION ALL
SELECT MIN_ID AS [ID], NULL AS Old_Id FROM (
SELECT FirstName, LastName, DOB, [Post Code], MIN([ID]) AS MIN_ID
FROM myTable99 a
GROUP BY FirstName, LastName, DOB, [Post Code]
HAVING COUNT(*) = 1) AS XXX
GO

DROP TABLE myTable99
DROP TABLE myNewTable99
GO

[/code]

Brett

8-)

X002548
Not Just a Number

15586 Posts

Posted - 2004-01-09 : 10:31:29

Or as 1...but You probably want the intermediary table...

Once it's cleaned up, make sure to put a unique contraint on the data...



    SELECT b.[ID], a.[ID] AS Old_ID
      FROM myTable99 a
INNER JOIN (SELECT a.[ID], a.FirstName, a.LastName, a.DOB, a.[Post Code] FROM myTable99 a
	 INNER JOIN (
	             SELECT FirstName, LastName, DOB, [Post Code], MIN ([ID]) AS MIN_ID
	               FROM myTable99
	           GROUP BY FirstName, LastName, DOB, [Post Code]) AS b
			 ON a.[ID] = b.MIN_ID) AS b
        ON a.FirstName = b.FirstName
       AND a.LastName = b.LastName
       AND a.DOB = b.DOB
       AND a.[Post Code] = b.[Post Code]
     WHERE a.[ID] <> b.[ID]
UNION ALL
SELECT MIN_ID AS [ID], NULL AS Old_Id FROM (
    SELECT FirstName, LastName, DOB, [Post Code], MIN([ID]) AS MIN_ID
      FROM myTable99 a
GROUP BY FirstName, LastName, DOB, [Post Code]
HAVING COUNT(*) = 1) AS XXX
GO

Brett

8-)

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2004-01-09 : 11:14:41

Cracked it, great, thanks guys

steve

Steve no function beer well without

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources