find_cmm DROP FUNCTION IF EXISTS find_cmm(BIGINT) CREATE OR REPLACE FUNCTION find_cmm(circ_id BIGINT) RETURNS SETOF INTEGER[] LANGUAGE plpgsql AS $function$ DECLARE aou INTEGER; ac INTEGER; au INTEGER; r INTEGER[]; BEGIN SELECT circ_lib FROM action.circulation WHERE id = circ_id INTO aou; SELECT target_copy FROM action.circulation WHERE id = circ_id INTO ac; SELECT usr FROM action.circulation WHERE id = circ_id INTO au; FOR r IN SELECT buildrows FROM action.find_circ_matrix_matchpoint(aou,ac,au,FALSE) LOOP RETURN NEXT r; END LOOP; RETURN; END $function$ circ_count circs 0 Open Circulations Circulation Status.Migrated.Count of Circs SELECT 'Closed Circulations', x_migrate::TEXT, COUNT(id) FROM action_circulation_legacy WHERE xact_finish IS NOT NULL GROUP BY 2 UNION ALL SELECT 'Open Circulations', x_migrate::TEXT, COUNT(id) FROM action_circulation_legacy WHERE xact_finish IS NULL GROUP BY 2 circ_count circs 1 Open Circulations Circulation Status.Count of Circs SELECT 'Closed Circulations', COUNT(id) FROM action_circulation WHERE xact_finish IS NOT NULL UNION ALL SELECT 'Open Circulations', COUNT(id) FROM action_circulation WHERE xact_finish IS NULL circ_by_orgunit circs 0 Circulations by Org Unit Circulations Count.Migrated.Org Unit SELECT COUNT(acirc.id), acirc.x_migrate::TEXT, aou.name FROM action_circulation_legacy acirc JOIN actor.org_unit aou ON aou.id = acirc.circ_lib WHERE acirc.xact_finish IS NULL GROUP BY 2, 3 circ_by_orgunit circs 1 Circulations by Org Unit Circulations Count.Org Unit SELECT COUNT(acirc.id), aou.name FROM action_circulation acirc JOIN actor.org_unit aou ON aou.id = acirc.circ_lib WHERE acirc.xact_finish IS NULL GROUP BY 2 circs_by_duration circs 0 Migrated Circulations by Duration, Fine and Max Fine Count of Circs.Duration.Fine.Max Fine.Migrated SELECT COUNT(id), duration, recurring_fine, max_fine, x_migrate::TEXT FROM action_circulation_legacy WHERE x_migrate = TRUE GROUP BY 2, 3, 4, 5 circs_by_duration circs 1 Circulations by Duration, Fine and Max Fine Count of Circs.Duration.Fine.Max Fine SELECT COUNT(id), duration, recurring_fine, max_fine FROM action_circulation GROUP BY 2, 3, 4 ORDER BY 2, 3, 4 circs_by_usrgroup circs 0 Circulations by Rules and Patron Group Count of Circs.Duration.Fine.Max Fine.User Group.Matchpoints SELECT COUNT(acirc.id), acirc.duration, acirc.recurring_fine, acirc.max_fine, pgt.name, x.buildrows FROM action_circulation_legacy acirc JOIN actor.usr au ON au.id = acirc.usr JOIN permission.grp_tree pgt ON pgt.id = au.profile JOIN (SELECT acirc.id, ARRAY_TO_STRING(find_cmm(acirc.id),',') AS buildrows FROM action_circulation_legacy acirc WHERE acirc.x_migrate = TRUE) x ON x.id = acirc.id WHERE acirc.x_migrate = TRUE GROUP BY 2, 3, 4, 5, 6 ORDER BY 2, 3, 4, 5, 6 circs_by_usrgroup circs 1 Circulations by Rules and Patron Group Count of Circs.Duration.Fine.Max Fine.User Group.Matchpoints SELECT COUNT(acirc.id), acirc.duration, acirc.recurring_fine, acirc.max_fine, pgt.name, x.buildrows FROM action_circulation acirc JOIN actor.usr au ON au.id = acirc.usr JOIN permission.grp_tree pgt ON pgt.id = au.profile JOIN (SELECT acirc.id, ARRAY_TO_STRING(find_cmm(acirc.id),',') AS buildrows FROM action_circulation acirc) x ON x.id = acirc.id GROUP BY 2, 3, 4, 5, 6 ORDER BY 2, 3, 4, 5, 6 circs_by_circmod circs 0 Circulations by Rules and Circulation Modifier Count of Circs.Duration.Fine.Max Fine.Circulation Modifier.Matchpoints SELECT COUNT(acirc.id), acirc.duration, acirc.recurring_fine, acirc.max_fine, ac.circ_modifier, x.buildrows FROM action_circulation_legacy acirc JOIN asset.copy ac ON ac.id = acirc.target_copy JOIN (SELECT acirc.id, ARRAY_TO_STRING(find_cmm(acirc.id),',') AS buildrows FROM action_circulation_legacy acirc WHERE acirc.x_migrate = TRUE) x ON x.id = acirc.id WHERE acirc.x_migrate = TRUE GROUP BY 2, 3, 4, 5, 6 ORDER BY 2, 3, 4, 5, 6 circs_by_circmod circs 1 Circulations by Rules and Circulation Modifier Count of Circs.Duration.Fine.Max Fine.Circulation Modifier.Matchpoints SELECT COUNT(acirc.id), acirc.duration, acirc.recurring_fine, acirc.max_fine, ac.circ_modifier, x.buildrows FROM action_circulation acirc JOIN asset.copy ac ON ac.id = acirc.target_copy JOIN (SELECT acirc.id, ARRAY_TO_STRING(find_cmm(acirc.id),',') AS buildrows FROM action_circulation acirc) x ON x.id = acirc.id GROUP BY 2, 3, 4, 5, 6 ORDER BY 2, 3, 4, 5, 6 circs_by_orgunit circs 0 Circulations by Rules and Org Unit Count of Circs.Duration.Fine.Max Fine.Library Branch SELECT COUNT(acirc.id), acirc.duration, acirc.recurring_fine, acirc.max_fine, aou.name FROM action_circulation_legacy acirc JOIN actor.org_unit aou ON aou.id = acirc.circ_lib WHERE acirc.x_migrate = TRUE GROUP BY 2, 3, 4, 5 ORDER BY 2, 3, 4, 5 circs_by_orgunit circs 1 Circulations by Rules and Org Unit Count of Circs.Duration.Fine.Max Fine.Library Branch SELECT COUNT(acirc.id), acirc.duration, acirc.recurring_fine, acirc.max_fine, aou.name FROM action_circulation acirc JOIN actor.org_unit aou ON aou.id = acirc.circ_lib GROUP BY 2, 3, 4, 5 ORDER BY 2, 3, 4, 5 non_cat_circs circs 0 Non-Cataloged Circulation Circulations Count.Migrated SELECT COUNT(id), x_migrate::TEXT FROM action_non_cataloged_circulation_legacy GROUP BY 2 non_cat_circs circs 1 Non-Cataloged Circulation Circulations Count SELECT COUNT(id) FROM action_non_cataloged_circulation in_house circs 0 In House Use In House Use Records.Migrated SELECT COUNT(id), x_migrate::TEXT FROM action_in_house_use_legacy GROUP BY 2 in_house circs 1 In House Use In House Use Records SELECT COUNT(id) FROM action_in_house_use circs_missing_rules circs 1 Circs Missing Rules Count.Field Missing SELECT COUNT(id), 'Duration Rule Value' FROM action_circulation WHERE duration IS NULL UNION ALL SELECT COUNT(id), 'Recurring Fine Rule Value' FROM action_circulation WHERE recurring_fine IS NULL UNION ALL SELECT COUNT(id), 'Max Fine Rule Value' FROM action_circulation WHERE max_fine IS NULL UNION ALL SELECT COUNT(id), 'Duration Rule' FROM action_circulation WHERE duration_rule IS NULL UNION ALL SELECT COUNT(id), 'Recurring Fine Rule' FROM action_circulation WHERE recurring_fine_rule IS NULL UNION ALL SELECT COUNT(id), 'Max Fine Rule' FROM action_circulation WHERE max_fine_rule IS NULL circ_open_by_item_status circs 0 Open Circulation and Status of Linked Items Count.Status SELECT COUNT(acirc.id), ccs.name FROM action.circulation acirc JOIN asset.copy ac ON ac.id = acirc.target_copy JOIN config.copy_status ccs ON ccs.id = ac.status WHERE acirc.xact_finish IS NULL AND acirc.checkin_time IS NULL AND acirc.id IN (SELECT id FROM action_circulation) GROUP BY 2 ORDER BY 2 holds holds 0 Migrated Holds Hold Type.Hold Count.Migrated SELECT 'Closed Holds', COUNT(id), x_migrate::TEXT FROM action_hold_request_legacy WHERE (expire_time::TIMESTAMP < now()) OR cancel_time IS NOT NULL OR fulfillment_time IS NOT NULL GROUP BY 3 UNION ALL SELECT 'Open Holds', COUNT(id), x_migrate::TEXT FROM action_hold_request_legacy WHERE (expire_time IS NULL OR expire_time::TIMESTAMP > now()) AND cancel_time IS NULL AND fulfillment_time IS NULL GROUP BY 3 holds_bytype holds 0 Migrated Holds By Type Hold Type.Hold Count.Migrated SELECT hold_type as "Hold Type", COUNT(id), x_migrate::TEXT FROM action_hold_request_legacy GROUP BY 1, 3 transit_open_by_item_status holds 0 Transit Copy Records and Status of Linked Items Count.Status SELECT COUNT(atc.id), ccs.name FROM action.transit_copy atc JOIN asset.copy ac ON ac.id = atc.target_copy JOIN config.copy_status ccs ON ccs.id = ac.status WHERE atc.id IN (SELECT id FROM action_transit_copy) AND atc.dest_recv_time IS NULL GROUP BY 2 ORDER BY 2 transit_copies_by_status holds 0 Status of Items with Count of Open In Transits Count.Status.Count of Open Transits SELECT COUNT(ac.id), ccs.name, SUM(CASE WHEN atc.id IS NULL THEN 0 ELSE 1 END) FROM asset.copy ac JOIN config.copy_status ccs ON ccs.id = ac.status LEFT JOIN (SELECT * FROM action.transit_copy WHERE id IN (SELECT id FROM action_transit_copy) AND dest_recv_time IS NULL) atc ON atc.target_copy = ac.id WHERE ac.id IN (SELECT id from asset_copy) GROUP BY 2 ORDER BY 2 hold_copies_by_status holds 0 Captured Holds with Status of Items Count of Captured Hold.Status of Item SELECT COUNT(ahr.id), ccs.name FROM action.hold_request ahr JOIN asset.copy ac ON ac.id = ahr.current_copy JOIN config.copy_status ccs ON ccs.id = ac.status WHERE ahr.capture_time IS NOT NULL AND ahr.fulfillment_time IS NULL and ahr.cancel_time IS NULL AND ahr.id IN (SELECT id FROM action_hold_request) GROUP BY 2 ORDER By 2 hold_depth holds 0 Depth of Unfilled Holds Count.Depth SELECT COUNT(ahr.id), ahr.selection_depth FROM action.hold_request ahr WHERE ahr.id IN (SELECT id FROM action_hold_request) AND ahr.cancel_time IS NULL AND ahr.capture_time IS NULL AND ahr.fulfillment_time IS NULL GROUP BY 2 ORDER BY 2 asset_copy_count Count of Copies by Library assets 0 Copy Count.Library.Migrated SELECT COUNT(ac.id), aou.name, ac.x_migrate::TEXT FROM asset_copy_legacy ac JOIN actor.org_unit aou ON aou.id = ac.circ_lib GROUP BY 2, 3 ORDER BY 2, 3 asset_deleted_copies Deleted Copies assets 0 Copy Count.Deleted.Migrated SELECT COUNT(ac.id), ac.deleted::TEXT, ac.x_migrate::TEXT FROM asset_copy_legacy ac GROUP BY 2, 3 asset_copies_by_status Copies by Status assets 0 Copy Count.Status.Migrated SELECT COUNT(ac.id), cs.name, ac.x_migrate::TEXT FROM asset_copy_legacy ac JOIN config.copy_status cs ON cs.id = ac.status GROUP BY 2, 3 ORDER BY 2, 3 asset_circ_mod_copies_count Copies by Circulation Modifier assets 0 Copy Count.Circulation Modifier.Migrated SELECT COUNT(ac.id), ac.circ_modifier, ac.x_migrate::TEXT FROM asset_copy_legacy ac GROUP BY 2, 3 ORDER BY 2, 3 asset_copy_notes Copy Notes assets 0 Note Count.Public.Migrated SELECT COUNT(acnote.id), acnote.pub::TEXT, acnote.x_migrate::TEXT FROM asset_copy_note_legacy acnote GROUP BY 2, 3 ORDER BY 2, 3 asset_copy_notes Copy Notes assets 1 Note Count.Public SELECT COUNT(acnote.id), acnote.pub::TEXT FROM asset_copy_note acnote GROUP BY 2 ORDER BY 2 asset_vols_by_lib Volumes by Library assets 0 Volume Count.Library.Migrated SELECT COUNT(acn.id), aou.name, acn.x_migrate::TEXT FROM asset_call_number_legacy acn JOIN actor_org_unit_legacy aou ON aou.id = acn.owning_lib GROUP BY 2, 3 ORDER BY 2, 3 asset_vols_by_lib Volumes by Library assets 1 Volume Count.Library SELECT COUNT(acn.id), aou.name FROM asset_call_number acn JOIN actor.org_unit aou ON aou.id = acn.owning_lib GROUP BY 2 ORDER BY 2 asset_cops_by_loc_and_org Copies by Location assets 0 Copy Count.Library.Circ Library.Migrated SELECT COUNT(ac.id), acl.name, aou.name, ac.x_migrate::TEXT FROM asset_copy_legacy ac JOIN asset.copy_location acl ON acl.id = ac.location JOIN actor.org_unit aou ON aou.id = ac.circ_lib GROUP BY 2, 3, 4 ORDER BY 2, 3, 4 asset_barcode_lengths Barcode Lengths by Library assets 0 Count of Barcode.Barcode Length.Library SELECT COUNT(ac.id), LENGTH(ac.barcode), aou.name FROM asset_copy_legacy ac JOIN actor.org_unit aou ON aou.id = ac.circ_lib WHERE ac.x_migrate = TRUE GROUP BY 2, 3 ORDER BY 3, 2 asset_barcode_patterns Common Barcode Starting Patterns assets 0 Count of Barcodes (greater than 10).Left 60% of Characters SELECT COUNT(ac.id), LEFT(ac.barcode,(ROUND(LENGTH(ac.barcode)*.6))::INT) FROM asset_copy_legacy ac WHERE ac.x_migrate = TRUE GROUP BY 2 HAVING COUNT(ac.id) > 10 ORDER BY 2 asset_barcode_incumbent_collisions Copy Barcode Incumbent Collisions assets 0 Collision Count SELECT COUNT(id) FROM asset_copy_legacy WHERE x_migrate = TRUE AND barcode ~* 'collision' and barcode ~* 'incumbent' Incumbent collisions are those where the migrated barcodes collide with existing barcodes in the database. asset_barcode_incumbent_collisions Copy Barcode Incumbent Collisions assets 1 Collision Count SELECT COUNT(id) FROM asset_copy WHERE barcode ~* 'collision' and barcode ~* 'incumbent' Incumbent collisions are those where the migrated barcodes collide with existing barcodes in the database. asset_barcode_internal_collisions Copy Barcode Internal Collisions assets 0 Collision Count SELECT COUNT(id) FROM asset_copy_legacy WHERE x_migrate = TRUE AND barcode ~* 'collision' and barcode ~* 'internal' Internal collisions are those where the migrated barcodes have conflicts within their list of barcodes. asset_barcode_internal_collisions Copy Barcode Internal Collisions assets 1 Collision Count SELECT COUNT(id) FROM asset_copy WHERE barcode ~* 'collision' and barcode ~* 'internal' Internal collisions are those where the migrated barcodes have conflicts within their list of barcodes. asset_barcode_collisions_shortlist Copy Barcode Collisions (first 20) assets 0 Collision List SELECT ac.barcode FROM asset_copy_legacy ac WHERE ac.barcode ~* 'collision' ORDER BY 1 LIMIT 20 This is a shortlist of patron barcode collisions that maxes out at 20. If there are more collisions we will need to run a custom report. asset_barcode_collisions_shortlist Copy Barcode Collisions (first 20) assets 1 Collision List SELECT ac.barcode FROM asset_copy ac WHERE ac.barcode ~* 'collision' ORDER BY 1 LIMIT 20 This is a shortlist of patron barcode collisions that maxes out at 20. If there are more collisions we will need to run a custom report. asset_barcode_collision_patterns Common Copy Barcode Collision Patterns assets 0 Number of Barcodes Matching Pattern Greater than 10.Left 60% of Characters SELECT COUNT(ac.id), LEFT(ac.barcode,(ROUND(LENGTH(ac.barcode)*.6))::INT) FROM asset_copy_legacy ac WHERE barcode ~* 'collision' GROUP BY 2 HAVING COUNT(ac.id) > 10 ORDER BY 2 asset_barcode_collision_patterns Common Copy Barcode Collision Patterns assets 1 Number of Barcodes Matching Pattern Greater than 10.Left 60% of Characters SELECT COUNT(ac.id), LEFT(ac.barcode,(ROUND(LENGTH(ac.barcode)*.6))::INT) FROM asset_copy ac WHERE barcode ~* 'collision' GROUP BY 2 HAVING COUNT(ac.id) > 10 ORDER BY 2 asset_stat_cats Copy Statistical Categories assets 0 Stat Cat Count.Library.Statistical Category SELECT COUNT(ac_sc.id), aou.name, ac_sc.name FROM asset_stat_cat_legacy ac_sc JOIN actor.org_unit aou ON aou.id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3 asset_stat_cats Copy Statistical Categories assets 1 Stat Cat Count.Library.Statistical Category SELECT COUNT(ac_sc.id), aou.name, ac_sc.name FROM asset_stat_cat ac_sc JOIN actor.org_unit aou ON aou.id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3 asset_stat_cat_entries Copy Stat Cat User Entries assets 0 Copy Stat Count.Library.Statistical Category SELECT COUNT(map.id), aou.name, ac_sc.name FROM asset_stat_cat_entry_copy_map_legacy map JOIN asset_stat_cat_legacy ac_sc ON ac_sc.id = map.stat_cat JOIN actor.org_unit aou ON aou.id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3 asset_stat_cat_entries Copy Stat Cat User Entries assets 1 Copy Stat Count.Library.Statistical Category SELECT COUNT(map.id), aou.name, ac_sc.name FROM asset_stat_cat_entry_copy_map map JOIN asset_stat_cat ac_sc ON ac_sc.id = map.stat_cat JOIN actor.org_unit aou ON aou. id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3 asset_copy_tags Copy Tags assets 0 Tag Count.Copy Tag Type.Copy Tag Label.Staff Note.Public SELECT COUNT(map.id), tag.tag_type, tag.label, tag.staff_note, tag.pub FROM asset_copy_tag tag JOIN asset_copy_tag_copy_map map ON map.tag = tag.id GROUP BY 2,3,4,5 ORDER BY 2,3 money_billing_voided Bills Voided And Not money 0 Count.Voided.Sum.Migrated SELECT COUNT(a.id), a.voided::TEXT, SUM(a.amount), a.x_migrate::TEXT FROM money_billing_legacy a GROUP BY 2, 4 ORDER BY 2, 4 money_billing_voided Bills Voided And Not money 1 Count.Voided.Sum SELECT COUNT(a.id), a.voided::TEXT, SUM(a.amount) FROM money_billing a GROUP BY 2 ORDER BY 2, 3 money_billing_by_type Bills by Type money 0 Count.Billing Type.Migrated SELECT COUNT(a.id), a.billing_type, a.x_migrate::TEXT FROM money_billing_legacy a GROUP BY 2, 3 ORDER BY 2, 3 money_billing_by_type Bills by Type money 1 Count.Billing Type SELECT COUNT(a.id), a.billing_type FROM money_billing a GROUP BY 2 ORDER BY 2 money_cash_payment Cash Payments money 0 Count.Voided.Sum.Migrated SELECT COUNT(a.id), a.voided::TEXT, SUM(a.amount), a.x_migrate::TEXT FROM money_cash_payment_legacy a GROUP BY 2, 4 ORDER BY 2, 4 money_cash_payment Cash Payments money 1 Count.Voided.Sum SELECT COUNT(a.id), a.voided::TEXT, SUM(a.amount) FROM money_cash_payment a GROUP BY 2 ORDER BY 2 money_check_payment Check Payments money 0 Count.Voided.Sum.Migrated SELECT COUNT(a.id), a.voided::TEXT, SUM(a.amount), a.x_migrate::TEXT FROM money_check_payment_legacy a GROUP BY 2, 4 ORDER BY 2, 4 money_forgive_payment Forgive Payments money 0 Count.Voided.Sum.Migrated SELECT COUNT(a.id), a.voided::TEXT, SUM(a.amount), a.x_migrate::TEXT FROM money_forgive_payment_legacy a GROUP BY 2, 4 ORDER BY 2, 4 money_forgive_payment Forgive Payments money 1 Count.Voided.Sum SELECT COUNT(a.id), a.voided::TEXT, SUM(a.amount) FROM money_forgive_paymen a GROUP BY 2 ORDER BY 2 money_goods_payment Goods Payments money 0 Count.Voided.Sum.Migrated SELECT COUNT(a.id), a.voided::TEXT, SUM(a.amount), a.x_migrate::TEXT FROM money_goods_payment_legacy a GROUP BY 2, 4 ORDER BY 2, 4 money_work_payment Work Payments money 0 Count.Voided.Sum.Migrated SELECT COUNT(a.id), a.voided::TEXT, SUM(a.amount), a.x_migrate::TEXT FROM money_work_payment_legacy a GROUP BY 2, 4 ORDER BY 2, 4 money_credit_card_payment Credit Card Payments money 0 Count.Voided.Sum.Migrated SELECT COUNT(a.id), a.voided::TEXT, SUM(a.amount), a.x_migrate::TEXT FROM money_credit_card_payment_legacy a GROUP BY 2, 4 ORDER BY 2, 4 money_credit_payment Credit Payments money 0 Count.Voided.Sum.Migrated SELECT COUNT(a.id), a.voided::TEXT, SUM(a.amount), a.x_migrate::TEXT FROM money_credit_payment_legacy a GROUP BY 2, 4 ORDER BY 2, 4 bibs Extracted Bibliographic Records bibs 0 Count.Deleted.Migrated SELECT COUNT(bre.id), bre.deleted::TEXT, bre.x_migrate::TEXT FROM biblio_record_entry_legacy bre GROUP BY 2, 3 ORDER BY 2, 3 False means the records are not deleted. bibs Extracted Bibliographic Records bibs 1 Count SELECT COUNT(eg) FROM bib_id_map bibswovolumes Bibliographic Records Without Volumes bibs 0 Count SELECT COUNT(eg) FROM bib_id_map where eg::INTEGER NOT IN (SELECT DISTINCT record FROM asset_call_number) bibs_notes Bib Record Notes bibs 0 Count.Migrated SELECT COUNT(b.id), b.x_migrate::TEXT FROM biblio_record_note_legacy b GROUP BY 2 bibs_notes Bib Record Notes bibs 1 Count SELECT COUNT(b.id) FROM biblio_record_note b bibs_peers Peer Bib Copies bibs 0 Count.Migrated SELECT COUNT(b.id), b.x_migrate::TEXT FROM biblio_peer_bib_copy_map_legacy b GROUP BY 2 bibs_peers Peer Bib Copies bibs 1 Count SELECT COUNT(b.id) FROM biblio_peer_bib_copy_map b bibs_parts Monograph Parts bibs 0 Count.Migrated SELECT COUNT(b.id), b.x_migrate::TEXT FROM biblio_monograph_part_legacy b GROUP BY 2 bibs_parts Monograph Parts bibs 1 Count SELECT COUNT(b.id) FROM biblio_monograph_part b usrsbyorg Patrons by Home Org actors 0 Count.Library.Deleted.Migrated SELECT COUNT(au.id), aou.name, au.deleted::TEXT, au.x_migrate::TEXT FROM actor_usr_legacy au JOIN actor.org_unit aou ON aou.id = au.home_ou GROUP BY 2, 3, 4 ORDER BY 2, 3, 4 usrsbypgt Patrons by Permission Group actors 0 Count.Permission Group.Migrated SELECT COUNT(au.id), pgt.name, au.x_migrate::TEXT FROM actor_usr_legacy au JOIN permission.grp_tree pgt ON pgt.id = au.profile GROUP BY 2, 3 ORDER BY 2, 3 active_usrs Patron by Active Status actors 0 Count of Users.Active.Migrated SELECT COUNT(id), active::TEXT, x_migrate::TEXT FROM actor_usr_legacy GROUP BY 2, 3 active_usrs Patrons by Active Status actors 1 Count of Users.Active SELECT COUNT(id), active::TEXT FROM actor_usr GROUP BY 2 active_usr_barcodes Patron Barcodes by Active Status actors 0 Count of Barcodes.Active.Migrated SELECT COUNT(id), active::TEXT, x_migrate::TEXT FROM actor_card_legacy GROUP BY 2, 3 active_usr_barcodes Patron Barcodes by Active Status actors 1 Count of Barcodes.Active SELECT COUNT(id), active::TEXT FROM actor_card GROUP BY 2 usr_barcode_lengths Barcode Lengths by Library actors 0 Count of Barcode.Barcode Length.Library SELECT COUNT(acard.id), LENGTH(acard.barcode), aou.name FROM actor_card_legacy acard JOIN actor_usr_legacy au ON au.id = acard.usr JOIN actor.org_unit aou ON aou.id = au.home_ou WHERE acard.x_migrate = TRUE GROUP BY 2, 3 ORDER BY 3, 2 usr_barcode_lengths Barcode Lengths by Library actors 1 Count of Barcode.Barcode Length.Library SELECT COUNT(acard.id), LENGTH(acard.barcode), aou.name FROM actor_card acard JOIN actor_usr au ON au.id = acard.usr JOIN actor.org_unit aou ON aou.id = au.home_ou GROUP BY 2, 3 ORDER BY 3, 2 usr_barcode_patterns Common Barcode Starting Patterns actors 0 Count of Barcodes (greater than 10).Left 60% of Characters SELECT COUNT(acard.id), LEFT(acard.barcode,(ROUND(LENGTH(acard.barcode)*.6))::INT) FROM actor_card_legacy acard WHERE acard.x_migrate = TRUE GROUP BY 2 HAVING COUNT(acard.id) > 10 ORDER BY 2 usr_barcode_patterns Common Barcode Starting Patterns actors 1 Count of Barcodes (greater than 10).Left 60% of Characters SELECT COUNT(acard.id), LEFT(acard.barcode,(ROUND(LENGTH(acard.barcode)*.6))::INT) FROM actor_card acard GROUP BY 2 HAVING COUNT(acard.id) > 10 ORDER BY 2 usr_barcode_collisions Patron Barcode Collisions actors 0 Collision Count SELECT COUNT(acard.id) FROM actor_card_legacy acard WHERE barcode ~* 'collision' AND x_migrate = TRUE usr_barcode_collisions Patron Barcode Collisions actors 1 Collision Count SELECT COUNT(acard.id) FROM actor_card acard WHERE barcode ~* 'collision' usr_barcode_collision_shortlist Patron Barcode Collisions (first 20) actors 0 Collision List SELECT acard.barcode FROM actor_card_legacy acard WHERE acard.barcode ~* 'collision' AND acard.x_migrate = TRUE ORDER BY 1 LIMIT 20 This is a shortlist of patron barcode collisions that maxes out at 20. If there are more collisions we will need to run a custom report. In some cases we may flag individual accounts to not migrate. usr_barcode_collision_shortlist Patron Barcode Collisions (first 20) actors 1 Collision List SELECT acard.barcode FROM actor_card acard WHERE acard.barcode ~* 'collision' ORDER BY 1 LIMIT 20 This is a shortlist of patron barcode collisions that maxes out at 20. If there are more collisions we will need to run a custom report. In some cases we may flag individual accounts to not migrate. usr_barcode_collision_patterns Common Patron Barcode Collision Patterns a.x_migrate actors 0 Number of Barcodes Matching Pattern Greater than 10.Left 60% of Characters SELECT COUNT(acard.id), LEFT(acard.barcode,(ROUND(LENGTH(acard.barcode)*.6))::INT) FROM actor_card_legacy acard WHERE acard.barcode ~* 'collision' AND acard.x_migrate = TRUE GROUP BY 2 HAVING COUNT(acard.id) > 10 ORDER BY 2 usr_barcode_collision_patterns Common Patron Barcode Collision Patterns a.x_migrate actors 1 Number of Barcodes Matching Pattern Greater than 10.Left 60% of Characters SELECT COUNT(acard.id), LEFT(acard.barcode,(ROUND(LENGTH(acard.barcode)*.6))::INT) FROM actor_card acard WHERE acard.barcode ~* 'collision' GROUP BY 2 HAVING COUNT(acard.id) > 10 ORDER BY 2 usr_addressses_status Patron Addresses by Valid Status actors 0 Count.Valid.Migrated SELECT COUNT(aua.id), valid::TEXT, x_migrate::TEXT FROM actor_usr_address_legacy aua GROUP BY 2, 3 usr_addressses_status Patron Addresses by Valid Status actors 1 Count.Valid SELECT COUNT(aua.id), valid::TEXT FROM actor_usr_address aua GROUP BY 2 usr_addresses_pending Patron Addresses by Pending Status actors 0 Count of Addresses.Pending.Migrated SELECT COUNT(aua.id), pending::TEXT, x_migrate::TEXT FROM actor_usr_address_legacy aua GROUP BY 2, 3 usr_addresses_pending Patron Addresses by Pending Status actors 1 Count of Addresses.Pending SELECT COUNT(aua.id), pending::TEXT FROM actor_usr_address aua GROUP BY 2 usr_messages Patron Messages actors 0 Count.Deleted.Migrated SELECT COUNT(aum.id), deleted::TEXT, x_migrate::TEXT FROM actor_usr_message_legacy aum GROUP BY 2, 3 usr_messages Patron Messages actors 1 Count.Deleted SELECT COUNT(aum.id), deleted::TEXT FROM actor_usr_message_legacy aum GROUP BY 2 usr_notes Patron Notes actors 0 Count.Public.Migrated SELECT COUNT(aun.id), pub::TEXT, x_migrate::TEXT FROM actor_usr_note_legacy aun GROUP BY 2, 3 usr_notes Patron Notes actors 1 Count.Public SELECT COUNT(aun.id), pub::TEXT FROM actor_usr_note aun GROUP BY 2 usr_stat_cats Patron Statistical Categories actors 0 Stat Cat Count.Library.Statistical Category.Migrated SELECT COUNT(au_sc.id), aou.name, au_sc.name, au_sc.x_migrate::TEXT FROM actor_stat_cat_legacy au_sc JOIN actor.org_unit aou ON aou.id = au_sc.owner GROUP BY 2, 3, 4 ORDER BY 2, 3, 4 usr_stat_cats Patron Statistical Categories actors 1 Stat Cat Count.Library.Statistical Category SELECT COUNT(au_sc.id), aou.name, au_sc.name FROM actor_stat_cat au_sc JOIN actor.org_unit aou ON aou.id = au_sc.owner GROUP BY 2, 3 ORDER BY 2, 3 usr_stat_cat_entries Patron Stat Cat User Entries actors 0 Patron Stat Count.Library.Statistical Category.Migrated SELECT COUNT(map.id), aou.name, au_sc.name, map.x_migrate::TEXT FROM actor_stat_cat_entry_usr_map_legacy map JOIN actor_stat_cat_legacy au_sc ON au_sc.id = map.stat_cat JOIN actor.org_unit aou ON aou.id = au_sc.owner GROUP BY 2, 3, 4 ORDER BY 2,3, 4 usr_stat_cat_entries Patron Stat Cat User Entries actors 1 Patron Stat Count.Library.Statistical Category SELECT COUNT(map.id), aou.name, au_sc.name FROM actor_stat_cat_entry_usr_map map JOIN actor_stat_cat au_sc ON au_sc.id = map.stat_cat JOIN actor.org_unit aou ON aou.id = au_sc.owner GROUP BY 2, 3 ORDER BY 2,3 usr_thresholds Patron Thresholds actors 0 Pateron Group.Org Unit.Penalty.Threshold SELECT pgt.name, aou.shortname, sp.name, p.threshold FROM permission_grp_penalty_threshold p JOIN actor.org_unit aou ON aou.id = p.org_unit JOIN permission.grp_tree pgt ON pgt.id = p.grp JOIN config.standing_penalty sp ON sp.id = p.penalty ORDER BY 2, 1, 3 fund_count 0 acq Migrated Funds Number of Funds.Migrated SELECT COUNT(id), x_migrate::TEXT FROM acq_fund_legacy GROUP BY 2; fund_count 1 acq Migrated Funds Number of Funds SELECT COUNT(id) FROM acq_fund; invoice_count 0 acq Migrated Invoices Number of Invoices.Migrated SELECT COUNT(id), x_migrate::TEXT FROM acq_invoice_legacy GROUP BY 2; invoice_count 1 acq Migrated Invoices Number of Funds SELECT COUNT(id) FROM acq_invoice; serials_mfhd_count serials 0 Migrated Serial MFHDs Number of MFHDs SELECT COUNT(id) FROM serial_record_entry dedupe_explain dedupe dedupe_process.asciidoc dedupe_format_count dedupe 0 Count of Items Matching By Format Count.Format(s) SELECT COUNT(id), search_formats FROM bib_matches GROUP BY 2 ORDER BY 2; dedupe_scores dedupe 0 Scoring Values in Matches Count.Scoring Information SELECT COUNT(id), 'Incumbent Records With Higher Scores to Incoming' FROM bib_matches WHERE incumbent_bib_score > incoming_bib_score UNION ALL SELECT COUNT(id), 'Incumbent Records With Equal Scores to Incoming' FROM bib_matches WHERE incumbent_bib_score = incoming_bib_score UNION ALL SELECT COUNT(id), 'Incumbent Records With Lower Scores to Incoming' FROM bib_matches WHERE incumbent_bib_score < incoming_bib_score ; dedupe_score_ranges dedupe 0 Count of Items Matching By Format Lowest Record Score.Largest Record Score.Record Set SELECT MIN(incumbent_bib_score), MAX(incumbent_bib_score), 'Incumbent Records' FROM bib_matches UNION ALL SELECT MIN(incoming_bib_score), MAX(incoming_bib_score), 'Incoming Records' FROM bib_matches ; dedupe_sample_set dedupe 0 Sample of 20 Matching Dedupe Record Sets Bib Being Merged Into.Bib Being Merged SELECT incumbent_bib, incoming_bib FROM bib_matches WHERE incumbent_bib_score >= incoming_bib_score LIMIT 20 ;