Voyager to Alma Migration Query: Identify Duplicate Call Numbers (Pass-through query)

When migrating from Voyager to Alma it may be helpful to identify and review duplicate call numbers.

This Pass-Through SQL Query (it will not run as is as a normal Access Query) finds pairs of records with the same call number, but different BIB IDs.  It compares the normalized call number, the way Voyager indexes them.  The query also checks that one MFHD_ID is greater than the other, so that each pair only shows up once in the results.  Bound-withs (different BIB, same holdings) are excluded.

Note that this query checks call number type.  It is set to look at LC, but can be altered to look at Dewey.

select
  bt1.bib_id as bib1
, bt2.bib_id as bib2
, mm1.display_call_no
, bt1.title_brief as title_1
, bt2.title_brief as title_2
from mfhd_master mm1
inner join bib_mfhd bm1 on mm1.mfhd_id = bm1.mfhd_id
inner join bib_text bt1 on bm1.bib_id = bt1.bib_id
inner join mfhd_master mm2 on mm1.normalized_call_no = mm2.normalized_call_no
inner join bib_mfhd bm2 on mm2.mfhd_id = bm2.mfhd_id
inner join bib_text bt2 on bm2.bib_id = bt2.bib_id
where bm1.bib_id != bm2.bib_id
and bm1.mfhd_id > bm2.mfhd_id
and mm1.call_no_type = '0' --change this to 1 if you use Dewey
order by mm1.normalized_call_no;

For other queries that may be useful, see my "Surviving Almanado: tips for a successful pre-implementation" presentation's Accompanying Materials.
 
If you need assistance in running custom SQL queries in Prepackaged Access Reports, consult the Voyager Customer Listserv.