Finding Duplicates

[Erich]

I have a company table and I would like to write a query that will return to
me any duplicate companies. However, it is a little more complicated then
just matching on exact company names. I would like it to give me duplicates
where x number of letters at the beginning of the company name match AND x
number of letters of the address match AND x number of letters of the city
match. I will be doing this in batches based on the first letter of the
company name. So for example I will first process all companies that start
with the letter "A".

So for all "A" companies I want to find companies where the first 5 letters
in the company name match and the first 5 characters of the address field
match and the first 5 characters of the city match. THANKS!!!

[Anith Sen]

Re: Finding Duplicates

Can you post simplified DDLs, some sample data & expected results? For
detail refer to : www.aspfaq.com/5006

--
- Anith
( Please reply to newsgroups only )

[Simon Hayes]

Re: Finding Duplicates

"Erich" <erich93063@hot mail.com> wrote in message news:<102gbomj7 gc84f7@corp.sup ernews.com>...[color=blue]
> I have a company table and I would like to write a query that will return to
> me any duplicate companies. However, it is a little more complicated then
> just matching on exact company names. I would like it to give me duplicates
> where x number of letters at the beginning of the company name match AND x
> number of letters of the address match AND x number of letters of the city
> match. I will be doing this in batches based on the first letter of the
> company name. So for example I will first process all companies that start
> with the letter "A".
>
> So for all "A" companies I want to find companies where the first 5 letters
> in the company name match and the first 5 characters of the address field
> match and the first 5 characters of the city match. THANKS!!![/color]

Something like this may work:

select t.*
from dbo.MyTable t
join
(
select
left(CompanyNam e, 5) as 'CompName',
left(Address, 5) as 'Addr',
left(City, 5) as 'City',
count(*) as 'Dupes'
from
dbo.MyTable
where
left(CompanyNam e, 1) = 'A'
group by
left(CompanyNam e, 5),
left(Address, 5),
left(City, 5)
having
count(*) > 1
) dt
on dt.CompName = left(t.CompanyN ame, 5)
and dt.Addr = left(t.Address, 5)
and dt.City = left(t.City, 5)


If this doesn't work as you expect, then please consider posting your
table DDL, as well as some sample data.

Simon

[Jason Sauer]

Re: Finding Duplicates

This may work as well, assuming a parameter is passed into the stored proc
or function for the first letter:

SELECT C1.CompanyID, C2.CompanyID,
C1.CompanyName, C2.CompanyName,
C1.Address, C2.Address, C1.City, C2.City
FROM Company C1 JOIN Company C2 ON
LEFT(C1.Company Name, 5) = LEFT(C2.Company Name, 5) AND
LEFT(C1.Address , 5) = LEFT(C2.Address , 5) AND
LEFT(C1.City, 5) = LEFT(C2.City, 5) AND
C1.CompanyID != C2.CompanyID
WHERE LEFT(C1.Company Name, 1) = @FirstLetter

You could also use a variable parameter instead of hard-coding "5" to allow
for more specific or more general matches.
ie, ... LEFT(C1.Company , @MatchLength) = LEFT(C2.Company Name, @MatchLength)

"Erich" <erich93063@hot mail.com> wrote in message
news:102gbomj7g c84f7@corp.supe rnews.com...[color=blue]
> I have a company table and I would like to write a query that will return[/color]
to[color=blue]
> me any duplicate companies. However, it is a little more complicated then
> just matching on exact company names. I would like it to give me[/color]
duplicates[color=blue]
> where x number of letters at the beginning of the company name match AND x
> number of letters of the address match AND x number of letters of the city
> match. I will be doing this in batches based on the first letter of the
> company name. So for example I will first process all companies that start
> with the letter "A".
>
> So for all "A" companies I want to find companies where the first 5[/color]
letters[color=blue]
> in the company name match and the first 5 characters of the address field
> match and the first 5 characters of the city match. THANKS!!!
>
>
>[/color]