SQL Query to find Patrons with Multiple (possibly duplicate) Names

Sierra, Millennium & Encore
,
1 
/*
 * Find patrons with multiple names, duplicate or otherwise
 *
 * Bob Gaydos <bgaydos@starklibrary.org>
 * August 13, 2024
 */
SET search_path = 'sierra_view';
WITH MultiplePatronNames AS
(
    SELECT
        patron_record_id,
        COUNT(patron_record_id) AS patron_names
    FROM patron_record_fullname
    GROUP BY
        patron_record_id
    HAVING
        COUNT(patron_record_id) > 1
)
SELECT
    rm.record_type_code || rm.record_num::VARCHAR(8) || 'a' AS "Patron Record Num",
    string_agg(COALESCE(pb.field_content, ''), ', ') AS "Patron Barcode(s)",
    mpn.patron_names AS "Names",
    CASE
        WHEN COALESCE(n.last_name, '') = ''
            THEN TRIM(TRIM(TRIM(COALESCE(n.first_name, '')) || ' '
              || TRIM(COALESCE(n.middle_name, ''))) || ' '
              || TRIM(COALESCE(n.suffix, '')))::TEXT
        ELSE
            TRIM(COALESCE(n.last_name, '')) || ', '
              || TRIM(TRIM(TRIM(COALESCE(n.first_name, '')) || ' '
              || TRIM(COALESCE(n.middle_name, ''))) || ' '
              || TRIM(COALESCE(n.suffix, '')))::TEXT
    END AS "Patron Name"
FROM MultiplePatronNames mpn
INNER JOIN record_metadata rm
   ON rm.id = mpn.patron_record_id
INNER JOIN patron_record_fullname n
   ON n.patron_record_id = mpn.patron_record_id
LEFT OUTER JOIN varfield pb
   ON pb.record_id = mpn.patron_record_id
  AND pb.varfield_type_code = 'b'
GROUP BY
    rm.record_type_code,
    rm.record_num,
    mpn.patron_names,
    n.last_name,
    n.first_name,
    n.middle_name,
    n.suffix
ORDER BY
    "Patron Record Num",
    "Patron Name"
;
1 Like
2

Thanks for sharing this, Bob! I just used it to clean up some patron accounts in our system. Fantastic stuff.