Duplicate Patrons with Email - Add to Existing Record Set
This query will populate a given record set with patrons that appear to be duplicates. It’s looking for duplicated by names, birthdates, and emails. You’ll see plenty of false positives, but it’ll find some duplicates that Polaris might miss.
DECLARE @PatronRecordSetID int;
/* PUT YOUR PATRON RECORD SET ID BELOW */
SET @PatronRecordSetID = 116005
/* Let's set up a temporary table to hold somde data. */
DECLARE @TempDupPatrons TABLE
(NameLast VARCHAR(250),
Birthdate DATETIME,
EmailAddress VARCHAR(64)
)
/* Now let's populate that table, looking for duplicated by birthdate and email */
INSERT @TempDupPatrons
SELECT
NameLast,
Birthdate,
EmailAddress
FROM
Polaris.Polaris.PatronRegistration WITH (NOLOCK)
GROUP BY
NameLast, Birthdate, EmailAddress
HAVING COUNT(*) > 1
ORDER BY
NameLast
DECLARE @PopulateRecordSetData TABLE
(RecordSetID INT,
PatronID INT
)
/* Prepare to populate a record set. */
INSERT @PopulateRecordSetData
SELECT
@PatronRecordSetID as "RecordSetID",
pr.PatronID
FROM
Polaris.Polaris.PatronRegistration pr WITH (NOLOCK)
JOIN
@TempDupPatrons as tdp on (tdp.NameLast = pr.Namelast and tdp.Birthdate = pr.Birthdate and tdp.EmailAddress = pr.EmailAddress)
ORDER BY
pr.NameLast,
pr.NameFirst,
pr.Birthdate
/* Populate the record set */
INSERT INTO Polaris.Polaris.PatronRecordSets
SELECT * FROM @PopulateRecordSetData