Merging Accounts Automatically after RE Integration

So, you ran your integration with RE, you have data flying back and forth between systems, you have people ordering tickets online creating duplicate accounts, and you fins that the integration made a couple thousand duplicates as well. now what? You need to merge lots of accounts, but merging is a bit different post integration. There is a "client merge" utility, but if you are like us, you don't want to merge all johnson's to one super-johnson account. You probably have a hundred or so nit-picky criteria that goes into deciding who to merge, which could never be handled by this utility.
So , here is what you do.
The birthday field is a widely unused field on the back-end of PE, and it turns out, you can auto-merge accounts based on birthdate. Also, the birthday field does not transfer to RE in the integration. The cltbirthdate field in the clients table is what you will use to mark your duplicates. Once duplicates are marked, use the client merge widard to merge the duplicate accounts based on birthdate.
Use something similar to the following code to mark the duplicates:

--safety first
--always use test system for testing
--don't run with scissors
--run at your own risk

declare @mbday datetime, @listsize int
set @mbday = '1/1/2100'
set @listsize = 10

--get mergelist . lowest acct number is always kept
SELECT top 5000
case when ClientCode_1 > ClientCode_2 then ClientCode_2 else ClientCode_1 end as keep,
case when ClientCode_1 < ClientCode_2 then ClientCode_2 else ClientCode_1 end as lose
into #m
FROM MyDuplicateListTable
group by
case when ClientCode_1 > ClientCode_2 then ClientCode_2 else ClientCode_1 end ,
case when ClientCode_1 < ClientCode_2 then ClientCode_2 else ClientCode_1 end


--delete loops
delete from #m
where keep in (select lose from #m)

--already merged recs out
delete from #m where
keep not in (select cltcode from clients with(nolock) )
or lose not in (select cltcode from clients with(nolock) )

--exclude more accounts based on your own criteria ...

--#ml = mergelist
select identity(int) as id, keep, lose into #ml from #m group by keep, lose order by keep
drop table #m
delete from #ml where id > @listsize


--now we go to transfer data that may be lost in the merging process
--copy email address to keep account (lower of the 2 numbers...)
update clients
set clients.cltemail = clt2.cltemail
from clients inner join
#ml on #ml.keep = clients.cltcode inner join
clients clt2 on clt2.cltcode = #ml.lose
where clt2.cltemail like '%_@%_.%__'
and (clients.cltemail like '' or clients.cltupdate < clt2.cltupdate)

--copy peologin to keep account
--existing:
update customerlogin
set customerlogin.cluserlogin = cl2.cluserlogin,
customerlogin.clpassword = cl2.clpassword
from #ml inner join
customerlogin on #ml.keep = customerlogin.clcontact inner join
customerlogin cl2 on cl2.clcontact = #ml.lose
where convert(varchar(30),cl2.clcontact) <> cl2.clpassword -- if pw defaults to acct num, do not copy

--new
insert into customerlogin (clcontact, cluserlogin, clpassword, clnextpwdchange)
select #ml.keep, cl.cluserlogin , cl.clpassword , '1/1/3000'
from #ml inner join
customerlogin cl on #ml.lose = cl.clcontact left outer join
customerlogin cl2 on cl2.clcontact = #ml.keep inner join
(select #ml.keep, max(#ml.lose) as maxloser from #ml inner join customerlogin on clcontact = #ml.lose group by #ml.keep)
as maxlose on maxlose.keep = #ml.keep and maxlose.maxloser = #ml.lose
where cl2.clcontact is null

--restrictions: get union list of 'yes' entries, copy list to both accts
select cltcode, cdprulecode
into #r
from
(
select #ml.keep as cltcode, cdprulecode
FROM ClientDataProtection with (nolock) inner join
#ml on #ml.keep = ClientDataProtection.cdpclientcode
where cdpflag = 1
union
select #ml.keep as cltcode, cdprulecode
FROM ClientDataProtection with (nolock) inner join
#ml on #ml.lose = ClientDataProtection.cdpclientcode
where cdpflag = 1
union
select #ml.lose as cltcode, cdprulecode
FROM ClientDataProtection with (nolock) inner join
#ml on #ml.keep = ClientDataProtection.cdpclientcode
where cdpflag = 1
union
select #ml.lose as cltcode, cdprulecode
FROM ClientDataProtection with (nolock) inner join
#ml on #ml.lose = ClientDataProtection.cdpclientcode
where cdpflag = 1
) as bla
group by cltcode, cdprulecode

--transfer status to existing...
update clientdataprotection set cdpflag = 1, cdpupdate = getdate()
from clientdataprotection inner join #r on #r.cltcode = clientdataprotection.cdpclientcode and #r.cdprulecode = clientdataprotection.cdprulecode

--insert records for recs that do not exist
insert into clientdataprotection
select #r.cdprulecode, #r.cltcode, 1, '1/1/1900', '1/1/3000', 1, getdate()
from #r left outer join
clientdataprotection cdp on cdp.cdpclientcode = #r.cltcode and cdp.cdprulecode = #r.cdprulecode
where cdp.cdpclientcode is null

--end merge data preservation section

--reset button for birthday field:
update clients set cltbirthdate = dateadd(ss,cltcode,'1/1/1900') where cltbirthdate <> dateadd(ss,cltcode,'1/1/1900')

--NOW MARK THE DUPLICATES
--mark cltbirthdate for merging. ac1 and ac1 both get same birthday,
--mark ac1
update clients
set cltbirthdate = dateadd(ss, #ml.keep ,@mbday)
from clients inner join #ml on #ml.keep = cltcode
--mark ac2
update clients
set cltbirthdate = dateadd(ss, #ml.keep ,@mbday)
from clients inner join #ml on #ml.lose = cltcode