Hi Chris,

I've been trying to work out a stragey for my merging but I'm struggling a little. :blush:

The merge stored procedure will accept two parameters, the source table and the merge criteria

1) Merge by Contact

2) Merge by Business

3) Merge by Address

Maybe I've started with the wrong one but I'm trying to work out the Merge by Address but I just can't get it right.

As mentioned previoulsy, the source table will always be in the following format:

create table dbo.PreMerge (

master_idint,

Titlevarchar(20),

Forenamevarchar(50),

Surnamevarchar(50),

CompanyNamevarchar(200),

CompanyName2varchar(200),

Address1varchar(200),

Address2varchar(200),

Address3varchar(200),

Address4varchar(200),

Townvarchar(100),

Countyvarchar(100),

Postcodevarchar(20),

Hierarchyint

)

I've applied the iTVF to get a match ratio for each column in the following sample data.

insert into PreMerge (master_id, Title, Forename, Surname, CompanyName, Address1, Address2, Town, Postcode, Hierarchy)

select 1547300,'Mr','E','Glowver',NULL,'21 Harcourt Crescent','Nuthall','Nottingham','NG14 6BT','20100309' union all

select 1581378,'Mrs','C E','Glowver',NULL,'21 Harcourt Crescent',NULL,'Nottingham','NG14 6BT','20100309' union all

select 43196681, NULL,NULL,'Glowveer',NULL,'21 Harcourt','Crescent',NULL,'NG14 6BT','20120501' union all

select 43196681, 'Mr','C J E','Glowser',NULL,'Harcourt','21 Harcourt Crescent',NULL,'NG14 6BT','20120501' union all

select 64273, 'Mrs', 'A', 'Tomlinson', NULL, '13 Harwood New Road', NULL, 'BLACKBURN', 'BB4 6QS', 2 union all

select 74575, 'Mr', 'M', 'Tomlinson', NULL, '13 Harwood New Road Great Harw', 'Great Harwood', 'BLACKBURN', 'BB4 6QS', 2

go

if object_id('PostMergeTest') > 0

drop table dbo.PostMergeTest

go

SELECT

r.master_id as retained_master_id,

t.master_id as deleted_master_id,

'#' AS '#',

isnull(FuzzyTitle.MatchRatio, 0) as Title_MatchRatio,

isnull(FuzzyForename.MatchRatio, 0) as Forename_MatchRatio,

isnull(FuzzySurname.MatchRatio, 0) as Surname_MatchRatio,

isnull(FuzzyAddress1.MatchRatio, 0) as Address1_MatchRatio,

isnull(FuzzyTown.MatchRatio, 0) as Town_MatchRatio,

'#' AS '##',

r.title as retained_title,

r.forename as retained_forename,

r.surname as retained_surname,

r.Address1 as retained_Address1,

r.Address2 as retained_Address2,

r.Town as retained_Town,

'#' AS '###',

t.title as deleted_title,

t.forename as deleted_forename,

t.Surname as deleted_surname,

t.Address1 as deleted_Address1,

t.Address2 as deleted_Address2,

t.Town as deleted_Town

into dbo.PostMergeTest

FROM dbo.PreMerge r

INNER JOIN dbo.PreMerge t

ON t.postcode = r.postcode

AND ((r.hierarchy<t.hierarchy) or (r.hierarchy=t.hierarchy and r.master_id>t.master_id))

-------------------------------------------------------------------------------------------------------------------------------------

CROSS APPLY [dbo].[FuzzyMatch_iTVF2k5] (r.Title, t.Title) FuzzyTitle-- Title

CROSS APPLY [dbo].[FuzzyMatch_iTVF2k5] (r.Forename, t.Forename) FuzzyForename-- Forename

CROSS APPLY [dbo].[FuzzyMatch_iTVF2k5] (r.Surname, t.Surname) FuzzySurname-- Surname

CROSS APPLY [dbo].[FuzzyMatch_iTVF2k5] (r.address1, t.address2) FuzzyAddress12-- Address1&Address2

CROSS APPLY [dbo].[FuzzyMatch_iTVF2k5] (r.address1, t.Address1) FuzzyAddress1-- Address1

CROSS APPLY [dbo].[FuzzyMatch_iTVF2k5] (r.Town, t.Town) FuzzyTown-- Town

--------------------------------------------------------------------------------------------------------------------------------------

order by Fuzzysurname.MatchRatio

go

I then created a new table to hold the duplicate IDs:

-- Match Level 1 = Surname, Address1 100% match

insert into dbo.FinalSiteDuplicates (retained_master_id, deleted_master_id, MatchLevel)

select

retained_master_id,

deleted_master_id,

1

from PostMergeTest

where (Surname_MatchRatio > 99 and Address1_MatchRatio > 99)

.

.

.

.

.

This find one duplicate but I've been trying different ration numbers and I jst can't get it to work.

am I doing this whole thing wrong? Is there a better way to do this?

Thanks in advance.