Skip to main content
ExLibris
  • Subscribe by RSS
  • Ex Libris Knowledge Center

    Voyager to Alma Migration Query: Counts of Patron Records Eligible for Deletion by Expiration or Purge Date

    Created By: Laura Guy (contact)
    Created on: 3/02/2021



    When migrating from Voyager to Alma, it may be helpful to identify the number of patrons present and how many of those are eligible for deletion by expiration date or purge date.

    These two queries, which can be run using Voyager Prepackaged Access Reports, will give you counts by expire date or purge date.

    Voyager patron records include two fields that the Patron Record Purge function may use for comparison: the expiration date and the purge date. You will need to select one of these.

     

    Count of Patrons by Expire Date:

    SELECT Year([expire_date]) & '-' & (IIf(Len(Month([expire_date]))=1,'0' & Month([expire_date]),Month([expire_date]))) & '-' & (IIf(Len(Day([expire_date]))=1,'0' & Day([expire_date]),Day([expire_date]))) AS ExpireDate, Count(PATRON.PATRON_ID) AS CountOfPATRON_ID FROM PATRON GROUP BY Year([expire_date]) & '-' & (IIf(Len(Month([expire_date]))=1,'0' & Month([expire_date]),Month([expire_date]))) & '-' & (IIf(Len(Day([expire_date]))=1,'0' & Day([expire_date]),Day([expire_date]))) ORDER BY Year([expire_date]) & '-' & (IIf(Len(Month([expire_date]))=1,'0' & Month([expire_date]),Month([expire_date]))) & '-' & (IIf(Len(Day([expire_date]))=1,'0' & Day([expire_date]),Day([expire_date])));

     

    Count of Patrons by Purge Date:

    SELECT Year([purge_date]) & '-' & (IIf(Len(Month([purge_date]))=1,'0' & Month([purge_date]),Month([purge_date]))) & '-' & (IIf(Len(Day([purge_date]))=1,'0' & Day([purge_date]),Day([purge_date]))) AS purgeDate, Count(PATRON.PATRON_ID) AS CountOfPATRON_ID FROM PATRON GROUP BY Year([purge_date]) & '-' & (IIf(Len(Month([purge_date]))=1,'0' & Month([purge_date]),Month([purge_date]))) & '-' & (IIf(Len(Day([purge_date]))=1,'0' & Day([purge_date]),Day([purge_date]))) ORDER BY Year([purge_date]) & '-' & (IIf(Len(Month([purge_date]))=1,'0' & Month([purge_date]),Month([purge_date]))) & '-' & (IIf(Len(Day([purge_date]))=1,'0' & Day([purge_date]),Day([purge_date])));

     

    If a date is shown with only two hyphens (“--“), these patron records contain no data in that date field. These records will be skipped by the patron record purge

    If expire dates or purge dates are in the distant past (e.g., 1930), you may wish to search for and review records individually before proceeding. This type of date may represent legacy data from a past system or an attempt to block a problem patron.

    If expire dates or purge dates are set too far in the future, the purge process will exclude those records. If the records are obsolete, you may wish to modify the dates to make them subject to deletion. Options for modifying existing patron data are included in the Appendix of this document.

    If your library performs batch loads of patrons, you will likely find large numbers of patron records with a single date near the end of a semester. Often, these dates make the most obvious choice of a cut-off.

    It is recommended that libraries only select a cut-off date for the purge that is a date in the past. A common cutoff date is one that allows for records that have expired in the last year to remain in the database. For example, if the purge is being requested in December 2017, the cutoff date could be December 1, 2016, or earlier.

     

    For other queries that may be useful, see my "Surviving Almanado: tips for a successful pre-implementation" presentation's Accompanying Materials.

    Posted as is. If you need assistance in running custom SQL queries in Prepackaged Access Reports, consult the Voyager Customer Listserv.