Removing duplicates from SQL Server 2005

I am currently working on project that is required to remove duplicate entries from Facility table, duplicates are based on address and city being the same. The duplicates would be recorded into an excel file for further examination and later expiry. I was looking for way to rank the entries and ran into this article Removing Duplicate Records using SQL Server 2005 that helped me write my query as follows.


CREATE TABLE #orgexpire

(

organizationuid BIGINT,

expirationrule VARCHAR (100)

);

WITH cte(labid, labname, cliacode, address, state, city)

AS (SELECT o.organizationuid,

o.organizationnm,

o.cliacode,

streetaddr = CASE

WHEN Len(Isnull(pl.streetaddr1, )) > 0 THEN

pl.streetaddr1

ELSE pl.streetaddr2

END,

pl.statecd,

pl.citydesc

FROM dbo.organization o

INNER JOIN dbo.entity e

ON o.organizationuid = e.entityuid

LEFT OUTER JOIN dbo.entitylocatorparticipation elp

ON elp.entityuid = e.entityuid

LEFT OUTER JOIN dbo.postallocator pl

ON elp.locatoruid = pl.postallocatoruid

WHERE o.cd = ‘LAB’ — facility type is lab

AND ( o.totime IS NULL

OR o.totime > Getdate() ) — Lab is not already expired

AND ( o.cliacode IS NULL

OR Len(o.cliacode) = 0 ) — There is no Clia code

AND o.addtime <= ’02/20/2009′

— Lab was not added after STD migration on 2/20/2009

),

dupcte(labid, address, city, ranking)

AS (SELECT labid,

address,

city,

ranking = Dense_rank() OVER (PARTITION BY address, city ORDER BY

(

SELECT

COUNT(p.actuid)

FROM dbo.participation p WHERE p.subjectentityuid =

labid)

DESC)

FROM cte

WHERE address IS NOT NULL

AND city IS NOT NULL)

INSERT INTO #orgexpire

SELECT labid,

‘6121-1 F002’

FROM dupcte

WHERE ranking > 1

SELECT * FROM #orgexpire

Advertisements

About Shashi Kuppa

I have 14 years of Information System experience. My background ranges from software architecture, analysis, design and development to team leadership and project management. My primary strengths include .Net system architecture, object oriented development using C#, web development using Asp.net and SQL server development, reporting and integration. I am an excellent team player, good communicator and have a proven track record for goal-oriented project leadership and management
This entry was posted in Development and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s