USP_REPORT_DUPLICATECONSTITUENTS
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @CHECKLASTNAME | bit | IN | |
| @CHECKFIRSTNAME | bit | IN | |
| @CHECKZIP | bit | IN | |
| @CHECKPRIMARYEMAIL | bit | IN | |
| @CHECKPRIMARYPHONE | bit | IN | |
| @FILTERBYDATE | bit | IN | |
| @ADDEDAFTERDATE | datetime | IN | |
| @EXCLUDERELATIONSHIPS | bit | IN |
Definition
Copy
create procedure dbo.USP_REPORT_DUPLICATECONSTITUENTS
(
@CHECKLASTNAME bit = 1,
@CHECKFIRSTNAME bit = 1,
@CHECKZIP bit = 0,
@CHECKPRIMARYEMAIL bit = 0,
@CHECKPRIMARYPHONE bit = 0,
@FILTERBYDATE bit = 0,
@ADDEDAFTERDATE datetime = null,
@EXCLUDERELATIONSHIPS bit = 1
)
with execute as owner
as
set @ADDEDAFTERDATE = dbo.UFN_DATE_GETEARLIESTTIME(@ADDEDAFTERDATE)
declare @PARAMSEXIST bit = (@CHECKLASTNAME | @CHECKFIRSTNAME | @CHECKZIP | @CHECKPRIMARYEMAIL | @CHECKPRIMARYPHONE)
declare @ORDERBYCLAUSE nvarchar(255) = ''
declare @SQLTOEXEC nvarchar(max)
set @SQLTOEXEC =
'select distinct top(500) C1.ID,C1.NAME
'
if @CHECKZIP = 1
set @SQLTOEXEC = @SQLTOEXEC + ',A1.DESCRIPTION ADDRESS'
else
set @SQLTOEXEC = @SQLTOEXEC + ',A.DESCRIPTION ADDRESS'
if @CHECKPRIMARYPHONE = 1
set @SQLTOEXEC = @SQLTOEXEC + ',P1.NUMBER PHONE'
else
set @SQLTOEXEC = @SQLTOEXEC + ',P.NUMBER PHONE'
if @CHECKPRIMARYEMAIL = 1
set @SQLTOEXEC = @SQLTOEXEC + ',E1.EMAILADDRESS EMAIL'
else
set @SQLTOEXEC = @SQLTOEXEC + ',E.EMAILADDRESS EMAIL'
set @SQLTOEXEC = @SQLTOEXEC + ',C1.LOOKUPID,C1.DATEADDED'
set @SQLTOEXEC = @SQLTOEXEC + '
from dbo.CONSTITUENT C1
'
if @CHECKLASTNAME = 1 or @CHECKFIRSTNAME = 1
begin
set @SQLTOEXEC = @SQLTOEXEC +
'inner join dbo.CONSTITUENT C2
'
end
if @CHECKLASTNAME = 1
begin
if @CHECKFIRSTNAME = 1
begin
set @ORDERBYCLAUSE = 'order by C1.NAME'
set @SQLTOEXEC = @SQLTOEXEC +
'on C1.KEYNAME = C2.KEYNAME and C1.FIRSTNAME = C2.FIRSTNAME and C1.ID <> C2.ID
'
end
else
begin
set @ORDERBYCLAUSE = 'order by C1.NAME'
set @SQLTOEXEC = @SQLTOEXEC +
'on C1.KEYNAME = C2.KEYNAME and C1.ID <> C2.ID
'
end
end
else
begin
if @CHECKFIRSTNAME = 1
begin
set @ORDERBYCLAUSE = 'order by C1.NAME'
set @SQLTOEXEC = @SQLTOEXEC +
'on C1.FIRSTNAME = C2.FIRSTNAME and C1.FIRSTNAME <> '''' and C1.ID <> C2.ID
'
end
end
if @CHECKFIRSTNAME = 1 or @CHECKLASTNAME = 1
begin
if @CHECKZIP = 1
begin
set @SQLTOEXEC = @SQLTOEXEC +
'inner join dbo.ADDRESS A1 on A1.CONSTITUENTID = C1.ID and A1.ISPRIMARY = 1
inner join dbo.ADDRESS A2 on A2.CONSTITUENTID = C2.ID and A1.POSTCODE = A2.POSTCODE and A1.POSTCODE <> '''' and A2.ISPRIMARY = 1
'
end
if @CHECKPRIMARYPHONE = 1
begin
set @SQLTOEXEC = @SQLTOEXEC +
'inner join dbo.PHONE P1 on P1.CONSTITUENTID = C1.ID and P1.ISPRIMARY = 1
inner join dbo.PHONE P2 on P2.CONSTITUENTID = C2.ID and P1.NUMBER = P2.NUMBER and P1.NUMBER <> '''' and P1.ISPRIMARY = 1
'
end
if @CHECKPRIMARYEMAIL = 1
begin
set @SQLTOEXEC = @SQLTOEXEC +
'inner join dbo.EMAILADDRESS E1 on E1.CONSTITUENTID = C1.ID and E1.ISPRIMARY = 1
inner join dbo.EMAILADDRESS E2 on E2.CONSTITUENTID = C2.ID and E2.EMAILADDRESS = E1.EMAILADDRESS and E1.EMAILADDRESS <> '''' and E2.ISPRIMARY = 1
'
end
end
else
begin
if @CHECKZIP = 1
begin
if @ORDERBYCLAUSE = ''
begin
set @ORDERBYCLAUSE = 'order by A1.DESCRIPTION'
end
set @SQLTOEXEC = @SQLTOEXEC +
'inner join dbo.ADDRESS A1 on A1.CONSTITUENTID = C1.ID and A1.ISPRIMARY = 1
inner join dbo.ADDRESS A2 on A1.CONSTITUENTID <> A2.CONSTITUENTID and A2.POSTCODE = A1.POSTCODE and A2.POSTCODE <> '''' and A2.ISPRIMARY = 1
'
end
if @CHECKPRIMARYPHONE = 1
begin
if @ORDERBYCLAUSE = ''
begin
set @ORDERBYCLAUSE = 'order by P1.NUMBER'
end
set @SQLTOEXEC = @SQLTOEXEC +
'inner join dbo.PHONE P1 on P1.CONSTITUENTID = C1.ID and P1.ISPRIMARY = 1
inner join dbo.PHONE P2 on P1.CONSTITUENTID <> P2.CONSTITUENTID and P2.NUMBER = P1.NUMBER and P2.NUMBER <> '''' and P2.ISPRIMARY = 1
'
end
if @CHECKPRIMARYEMAIL = 1
begin
if @ORDERBYCLAUSE = ''
begin
set @ORDERBYCLAUSE = 'order by E1.EMAILADDRESS'
end
set @SQLTOEXEC = @SQLTOEXEC +
'inner join dbo.EMAILADDRESS E1 on E1.CONSTITUENTID = C1.ID and E1.ISPRIMARY = 1
inner join dbo.EMAILADDRESS E2 on E1.CONSTITUENTID <> E2.CONSTITUENTID and E2.EMAILADDRESS = E1.EMAILADDRESS and E2.EMAILADDRESS <> '''' and E2.ISPRIMARY = 1
'
end
end
if @CHECKZIP = 0
begin
set @SQLTOEXEC = @SQLTOEXEC +
'left outer join dbo.[ADDRESS] A on A.CONSTITUENTID = C1.ID and A.ISPRIMARY = 1
'
end
if @CHECKPRIMARYPHONE = 0
begin
set @SQLTOEXEC = @SQLTOEXEC +
'left outer join dbo.PHONE P on P.CONSTITUENTID = C1.ID and P.ISPRIMARY = 1
'
end
if @CHECKPRIMARYEMAIL = 0
begin
set @SQLTOEXEC = @SQLTOEXEC +
'left outer join dbo.EMAILADDRESS E on E.CONSTITUENTID = C1.ID and E.ISPRIMARY = 1
'
end
if @PARAMSEXIST = 1
begin
if @EXCLUDERELATIONSHIPS = 1
begin
if @CHECKLASTNAME = 0 and @CHECKFIRSTNAME = 0
begin
declare @INNERJOINSTARTED bit = 0
if @CHECKZIP = 1
begin
set @INNERJOINSTARTED = 1
set @SQLTOEXEC = @SQLTOEXEC +
'inner join dbo.CONSTITUENT C2 on A2.CONSTITUENTID = C2.ID
'
end
if @CHECKPRIMARYPHONE = 1
begin
if @INNERJOINSTARTED = 0
begin
set @INNERJOINSTARTED = 1
set @SQLTOEXEC = @SQLTOEXEC +
'inner join dbo.CONSTITUENT C2 on P2.CONSTITUENTID = C2.ID
'
end
else
begin
set @SQLTOEXEC = @SQLTOEXEC +
'or P2.CONSTITUENTID = C2.ID
'
end
end
if @CHECKPRIMARYEMAIL = 1
begin
if @INNERJOINSTARTED = 0
begin
set @INNERJOINSTARTED = 1
set @SQLTOEXEC = @SQLTOEXEC +
'inner join dbo.CONSTITUENT C2 on E2.CONSTITUENTID = C2.ID
'
end
else
begin
set @SQLTOEXEC = @SQLTOEXEC +
'or E2.CONSTITUENTID = C2.ID
'
end
end
end
set @SQLTOEXEC = @SQLTOEXEC +
'where not exists(select 1 from dbo.RELATIONSHIP where RELATIONSHIPCONSTITUENTID = C1.ID and RECIPROCALCONSTITUENTID = C2.ID)
'
if @CHECKFIRSTNAME = 0 and @CHECKLASTNAME = 0
begin
set @SQLTOEXEC = @SQLTOEXEC +
'and C1.ISGROUP = 0 and C2.ISGROUP = 0
'
end
if @FILTERBYDATE = 1
begin
set @SQLTOEXEC = @SQLTOEXEC +
'and (C1.DATEADDED > @ADDEDAFTERDATE and C2.DATEADDED > @ADDEDAFTERDATE)
'
end
end
else
begin
if @FILTERBYDATE = 1
begin
set @SQLTOEXEC = @SQLTOEXEC +
'where (C1.DATEADDED > @ADDEDAFTERDATE)
'
end
end
set @SQLTOEXEC = @SQLTOEXEC + @ORDERBYCLAUSE
exec sp_executesql @SQLTOEXEC, N'@ADDEDAFTERDATE datetime', @ADDEDAFTERDATE = @ADDEDAFTERDATE
end