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

    Report of high-use items

    • Article Type: General
    • Product: Aleph
    • Product Version: 20
    • Relevant for Installation Type: Dedicated-Direct; Direct; Local;

    Desired Outcome Goal:
    Generate a report listing the number of loans and renewals for items which have been loaned/renewed more than x times.

    Execute following SQL, changing "XXX01" and "XXX50" to your local libraries.

    The right-hand column has the count; the second from the right, the circ event type: event types 50-7 are loans and 62-4 are renewals. The ./xxx50/tab/tab_events.eng includes text describing each event type.

    The "count(*) > 4" could be adjusted as desired.

    > s+ xxx50

    spool circ_count.lst

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

    select /*+ DYNAMIC_SAMPLING(2) ALL_ROWS */ rtrim (z13_title) || ',' || rtrim (substr(z30_barcode,1,16)) || ',' || Z35_REC_KEY || '-' || rtrim (Z35_ITEM_SEQUENCE), rtrim (count(*))
    from z30, xxx01.z13, z103, z35
    where Z35_REC_KEY || LPAD (Z35_ITEM_SEQUENCE,6,'0') = z30_rec_key and
    substr (z103_rec_key_1,6,9) = z13_rec_key and z103_rec_key_1 like 'XXX01%' and
    substr (z103_rec_key,6,9) = substr (z30_rec_key,1,9) and z103_rec_key like 'XXX50%' and
    (z35_event_type like '5%' or z35_event_type in ('62','63','64'))
    group by rtrim(z13_title)||','||rtrim(substr(z30_barcode,1,16))||','||z35_REC_KEY||'-'||rtrim(Z35_ITEM_SEQUENCE)
    having count(*)>4
    order by count(*) desc;

    spool off

    Additional Information

    The above is intended as a supplement to cir-14 ("Report of Items in High Demand"), which reports on items with more than x hold requests.

    Related article:  SQL to locate items with fewer (or more) than x loans .

    Category: Circulation

    • Article last edited: 10/8/2013