create table dbo.PreMerge (
	master_id		int,
	Title			varchar(20),
	Forename		varchar(50),
	Surname			varchar(50),
	CompanyName		varchar(200),
	CompanyName2		varchar(200),
	Address1		varchar(200),
	Address2		varchar(200),
	Address3		varchar(200),
	Address4		varchar(200),
	Town			varchar(100),
	County			varchar(100),
	Postcode		varchar(20),
	Hierarchy		int
	)

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,	'Mr',	'C',	'Wallis',		NULL,	'116 Lynch Gate',	NULL,	'Lydbury North',	'SY7 8AE',	'20100309' union all
select 43196681, 'Mrs',	'X',	'Collows',	NULL,	'14 Ferndown Road',	'Eltham',	'London',	'SE4 5JP',	'20120501' union all
select 43198564, 'Mrs',	'Z',	'Brogdenson',	NULL,	'36 Rhodes Park',	NULL,	'North Berwick',	'EH11 5QA',	'20120501' union all
select 1581378,	'Mr',	'Y',	'Walls',		NULL,	'117 Lynch Gate',	NULL,	'Lydbury South',	'SX7 8OE',	'20100309' union all
select 1547300,	'Mr',	'P',	'Glowver',	NULL,	'21 Harcourt',	'Nuthall',	'Notts', 'NG14 6BT',	'20100309'

select a.master_id as retained_id, b.master_id as Duplicate_id,
a.title as retained_title, b.title as dropped_title
a.forename as retained_forename, b.forename as dropped_forename
.
.
.
.
from PreMerge as a
inner join Premerge as b
on left(a.Surname,5)=left(b.Surname,5) 
and left(a.forename,1) = left(B.forename,1)
and a.postcode=b.postcode
where  ((a.hierarchy<b.hierarchy) or (a.hierarchy=b.hierarchy and a.MergeID>b.MergeID))
and left(A.companynameny,8) = left(B.companyname,8)
and left(A.address1,8) = left(B.address1,8)
and a.postcode is not null and b.postcode is not null
and a.address1 is not null and b.address1 is not null
and a.forename is not null and b.forename is not null
and a.companyname is not null and b.companyname is not null