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

    SQL to find number of items in collections that were returned late

    • Article Type: General
    • Product: Aleph
    • Product Version: 18.01

    Description:
    Is there a report or SQL to show the number of items in each collection that were returned late?

    Ideally, it would show how many days late the items were returned.

    Resolution:
    The Description in the z31 cash record created for an overdue item looks like this:

    z31_description ..............Late return <0004 0000 2 .10>

    The first number is the number of days the item is overdue.

    The following SQL script will give you a count of the number of instances for each number of days overdue, ordered by collection:

    set echo off
    set pause off
    set term off
    set show off
    set feed off
    set verify off
    set heading off
    set pagesize 58

    spool days_overdue_by_collection.lst

    select z30_collection, substr (z31_description,14,4), count(*) from z30, z31 where substr (z31_key,1,15) = z30_rec_key and z31_description like 'Late return <%'
    group by z30_collection, substr (z31_description,14,4)
    order by z30_collection, substr (z31_description,14,4) asc;

    spool off

    Note: If you need *sublibrary* by collection, the following select can be used instead:

    select z30_sub_library, z30_collection, substr (z31_description,14,4), count(*) from z30, z31 where substr (z31_key,1,15) = z30_rec_key and z31_description like 'Late return <%'
    group by z30_sub_library, z30_collection, substr (z31_description,14,4)
    order by z30_sub_library, z30_collection, substr (z31_description,14,4) asc;


    • Article last edited: 10/8/2013