Duplicate Patrons - 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 and birthdates. 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 = 32900
/* Let's make a temporary table to hold some patron records */
DECLARE @TempDupPatrons TABLE
(NameLast VARCHAR(250),
Birthdate DATETIME)
/* Now let's populate that table */
INSERT @TempDupPatrons
SELECT
NameLast,
Birthdate
FROM
Polaris.Polaris.PatronRegistration WITH (NOLOCK)
GROUP BY
NameLast,
Birthdate
HAVING COUNT(*) > 1
ORDER BY
NameLast
/* Now take the data from @TempDupPatrons and prepare it for a record set. */
DECLARE @PopulateRecordSetData TABLE
(RecordSetID INT,
PatronID INT
)
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)
ORDER BY
pr.NameLast,
pr.NameFirst,
pr.Birthdate
/* Now let's populate that record set. */
INSERT INTO Polaris.Polaris.PatronRecordSets
select * from @PopulateRecordSetData