Hi All,
Lets Get into Scenario,
1. Find all the duplicate records in a table where firstname and lastname of the one record is same with firstname and lastname of other record. E.g
| First Name |
Last Name |
| Hiren |
Solanki |
| Hiren |
Solanki |
above are the duplicates
2. Find all the duplicate records in a table where firstname and lastname of the one record is verbally same with firstname and lastname of other record E.g
| First Name |
Last Name |
| Hiren |
Solanki |
| Hyren |
Solanki |
above are the duplicates.
So Lets Create one Temp. Table
using Following Syntax
1. Create Temperory Table
create table #tempProfile
(
ID int,
FirstName varchar(max),
LastName varchar(max),
Designation varchar(max)
)
2. Fill The Data Into Table
insert into #tempProfile
select 1, 'Hiren','Solanki','Devloper'
union all
select 2,'Hyren','Solanki','Devloper'
union all
select 3,'Virang','Patel','Sr.Devloper'
union all
select 4 ,'Rajesh','Thakur','Tech. Lead'
union all
select 5 ,'Sandeep','Ramani','Devloper'
union all
select 6 ,'Sandip','Ramani','Devloper'
union all
select 7 ,'Bharat','Arora','Project Manager'
union all
select 8 ,'Hiren','Solanki','CRM consultant'
union all
select 9,'Rajesh','Thakur','CRM Consultant'
Now The Query to Retrive to First Scenario. ( Exact Match )
WITH Temp_CTE(FirstName,LastName,DupCount)
AS
(
SELECT FirstName,LastName,COUNT(*) DupCount
FROM #tempProfile
GROUP BY FirstName,LastName
HAVING COUNT(*) > 1
)
SELECT * FROM #tempProfile TP
WHERE
EXISTS
(
SELECT 1
FROM Temp_CTE
WHERE FirstName = TP.FirstName AND LastName = TP.LastName
)
and Now for the 2nd Scenarion (Verbal Match)
WITH Temp_CTE(FirstName,LastName,DupCount)AS
(
SELECT SOUNDEX(FirstName),SOUNDEX(LastName),COUNT(*) DupCount
FROM #tempProfile
GROUP BY SOUNDEX(FirstName),SOUNDEX(LastName)
HAVING COUNT(*) > 1
)
SELECT * FROM #tempProfile TP
WHERE
EXISTS
(
SELECT 1 FROM Temp_CTE
WHERE FirstName = SOUNDEX(TP.FirstName)
AND LastName = SOUNDEX(TP.LastName)
)
Done.
below is the full code if you Wanna have a Test.
create table #tempProfile(ID int,FirstName varchar(max),LastName varchar(max),Designation varchar(max))
GO
insert into #tempProfile
Select 1, 'Hiren','Solanki','Devloper'
union all
select 2,'Hyren','Solanki','Devloper'
union all
select 3,'Virang','Patel','Sr.Devloper'
union all
select 4 ,'Rajesh','Thakur','Tech. Lead'
union all
select 5 ,'Sandeep','Ramani','Devloper'
union all
select 6 ,'Sandip','Ramani','Devloper'
union all
select 7 ,'Bharat','Arora','Project Manager'
union all
select 8 ,'Hiren','Solanki','CRM consultant'
union all
select 9,'Rajesh','Thakur','CRM Consultant'
GO
WITH Temp_CTE(FirstName,LastName,DupCount)
AS
(
SELECT FirstName,LastName,COUNT(*) DupCount
FROM #tempProfile
GROUP BY FirstName,LastName
HAVING COUNT(*) > 1
)
SELECT * FROM #tempProfile TP
WHERE
EXISTS
(
SELECT 1
FROM Temp_CTE
WHERE FirstName = TP.FirstName AND LastName = TP.LastName
)
GO
WITH Temp_CTE(FirstName,LastName,DupCount)AS
(
SELECT SOUNDEX(FirstName),SOUNDEX(LastName),COUNT(*) DupCount
FROM #tempProfile
GROUP BY SOUNDEX(FirstName),SOUNDEX(LastName)
HAVING COUNT(*) > 1
)
SELECT * FROM #tempProfile TP
WHERE
EXISTS
(
SELECT 1 FROM Temp_CTE
WHERE FirstName = SOUNDEX(TP.FirstName)
AND LastName = SOUNDEX(TP.LastName)
)
drop table #tempProfile
