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

    Report of items which have never been checked out (for weeding)

    • Article Type: General
    • Product: Aleph
    • Product Version: 20, 21, 22, 23

    Description:
    We need a report that lists what items have never been checked out (and are prime candidates to be weeded). Please explain the best way to do this.

    Resolution:
    You can use the p_ret_adm_01 General (ADM) Retrieval form to find items with No. of loans 0, but you will almost certainly want to limit the results to items which are *not* ISSUE items and items purchased before some date, say, 20170101.


    The following SQL script can be used to do that (with additions such as "and z30_material ^= 'ISSUE' to the where clause):
    s+ abc50
    set echo off
    set pause off
    set feed off
    set heading off
    spool sqlspool
    SELECT /*+ DYNAMIC_SAMPLING(2) ALL_ROWS */ z30_collection, z30_call_no, Z30_BARCODE, Z13_TITLE from ABC01.Z13, Z30, Z103 where Z13_REC_KEY = substr (Z103_REC_KEY_1,6,9) and substr (Z103_REC_KEY_1,1,5) = 'ABC01'
    and substr (Z103_REC_KEY,6,9) = substr (Z30_REC_KEY,1,9) and z30_open_date < '20170101' and z30_sub_library = 'WO' and z30_no_loans = 0 order by z30_collection, z30_call_no;
    spool off
    exit

    See also the Article: SQL to locate items with fewer (or more) than x loans .


    [From Alan Exelby, University of East Anglia, on Global Aleph Users list:]

    A couple of people were interested in our SQLs, so, with apologies for the delay, here they are:

    1. Fairly simple SQL listing items with the total ‘use count’ from z30:
    select Z30_CALL_NO||'\'||z13_title||'\'||z30_no_loans||'\'||z30_date_last_return||'\'||z30_barcode
    from z13, z30
    where z13_rec_key = substr(z30_rec_key,1,9)
    and z30_sub_library like 'CR%'
    and z30_item_process_status is null
    order by Z30_CALL_NO,z13_title;


    (This is a quick-and-dirty report primariy intended to allow our short-loan section to identify un-used items and assess whether to send them back to normal stock.)

    2. More advanced SQL counting z36H records by Item for a specific period and reporting those with a total higher than a specified number:
    SELECT z30_call_no||'\'||COUNT (z36h_loan_date)||'\'||
    z36h_rec_key||'\'||SUBSTR (z13_title, 1, 50)||'\'||z30_barcode
    FROM z36h, z30, z13
    WHERE z30_rec_key = z36h_rec_key
    AND SUBSTR (z30_rec_key, 1, 9) = z13_rec_key
    AND z36h_loan_date >= '20100101'
    AND z36h_loan_date IS NOT NULL
    AND z30_item_process_status IS NULL
    GROUP BY z30_call_no, z36h_rec_key, SUBSTR (z13_title, 1, 50), z30_barcode
    HAVING COUNT (z36h_loan_date) >= 4
    ORDER BY z30_call_no, z36h_rec_key


    (This is for a report on books that have been heavily-used recently, designed for our Faculty Librarians as one of the tools on which they base decisions on buying extra copies or electronic editions.)


    3. Similar to no.2 above but supposedly counting z36H records together for a title-level count rather than Item-level:
    set pause off head off
    set lines 200
    select distinct
    title||'\'||
    call_no||'\'||
    qty
    from

    (select count(z30_barcode) as qty, z30_call_no as call_no, z13_title as title
    from z36h, z30, z13
    where z30_item_process_status is null and
    substr(z30_rec_key, 1,9) = substr(z36H_rec_key, 1,9) and
    substr(z36H_rec_key, 1,9) = substr(z13_rec_key, 1,9) and
    z36H_loan_date >= '20100101' and
    z30_barcode = z30_barcode and
    z36H_loan_date is not null
    group by z30_barcode, z13_title, z30_call_no
    order by z30_barcode, z13_title, z30_call_no
    )
    where qty >=4


    This third option was developed before no.2, and not by me; I was never convinced the results were 100% reliable (comparison of results with figures in the GUI suggested ca. 95%), but our Faculty Librarians, who had requested the report in the first place, changed their minds and decided they wanted it by item-level anyway, so we developed no.2 above and abandoned this one; it has not been used in several years.

    General notes re. these three SQLs:

    - we run SQLs with a delimiter and generally expect the recipients to use that delimiter for conversion to Excel; the delimiter we normally use is the left-slash (\), which we find is the only basic keyboard character that does not also occur in the data – many years ago, we tried to get a tab inserted as a delimiter, but even very experienced SQL-ers could not get the normal inputs to work, so we gave up and standardised on the left slash. If you prefer another delimiter, just use it instead of the left slash.

    - the versions above are base-line versions and are normally used with fine-tuning of the ‘where’ clauses as needed

    [From Frank Vandepitte <Frank.Vandepitte@UGENT.BE>:]

    Another way would be to have an html table output, which is readable and can also easily be imported in excel
    Almost all of our reporting is done in that way
    (result : http://aleph.ugent.be/adhoc/test.html)
    BR, Frank

    ----------------------------------------------------------------
    SET PAGESIZE 50000
    SET PAUSE OFF
    SET FEEDBACK OFF
    SET TERMOUT OFF
    SET MARKUP HTML ON SPOOL ON
    COLUMN SYSTIME HEADING "JUST TESTING ;-)" FORMAT A100
    SPOOL /exlibris/aleph/u18_1/alephe/apache/htdocs/adhoc/test.html
    SELECT TO_CHAR(SYSDATE,'DD-MON-RRRR HH24:MI:SS') SYSTIME FROM DUAL;
    SELECT
    Z30_CALL_NO CN, Z13_TITLE TI, Z30_NO_LOANS NR, Z30_DATE_LAST_RETURN RT, Z30_BARCODE BC
    FROM Z13,Z30
    WHERE
    Z13_REC_KEY = SUBSTR(Z30_REC_KEY,1,9)
    AND Z30_SUB_LIBRARY LIKE 'PS01%'
    AND Z30_ITEM_PROCESS_STATUS IS NULL
    ORDER BY Z30_CALL_NO,Z13_TITLE;
    SPOOL OFF


    <end Frank Vandepitte>

    Additional Information

    See also Article 000036070    SQL to locate items with fewer (or more) than x loans


    • Article last edited: 10/23/2014