Skip to main content
ExLibris
  • Subscribe by RSS
  • ExLibris Dev

    Copy/volume statistics

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

    Description:
    I am trying to get statistics on the number of items or volumes by collection /or sub-library, but I can’t figure out how to do this. Does anyone know how to retrieve these stats? I can get the stats for the titles, but I need to drill down to actual copies or volumes.

    Resolution:
    ARC has this, but there is no ALEPH GUI Services canned report that does this. SQL can be used. (See below.) The SQL could be called from a Custom Services job -- so that non-techical staff could submit it.


    [From Steve Thomas, University of Tennessee, Knoxville, on ALEPH-NA:]

    Below is a SQL script that I use to pull counts from the item table at the end of the fiscal year. You could eliminate the open_date portion of the query if you are not interested in a particular time period. Feel free to write me directly (sthoma15@utk.edu) if you have questions.

    -- db03.sql srt July 2004
    -- title and item counts from Z30 by sub-library and material type
    -- edited 2007 to add item_process_status list
    SET PAUSE OFF
    SET LINESIZE 40
    SET PAGESIZE 2000
    SET TERM OFF

    SPOOL db03

    COLUMN z30_sub_library HEADING 'Lib.'
    COLUMN z30_material HEADING 'Type'
    COLUMN count(distinct(substr(z30_rec_key,1,9))) HEADING 'Bibs'
    COLUMN count(*) HEADING 'Items'

    SELECT z30_sub_library,
    z30_material,
    count(distinct(substr(z30_rec_key,1,9))),
    count(*)
    FROM z30
    WHERE z30_open_date <= 20070630
    AND (z30_item_process_status IS NULL
    OR z30_item_process_status NOT IN
    ('BA', 'CA', 'CL', 'IP', 'NA', 'NO', 'NP', 'OI', 'OP', 'OR', 'SC'))
    GROUP BY z30_sub_library, z30_material
    /
    SET TERM ON
    SET PAUSE ON
    SPOOL OFF


    [From Alan Exelby, University of East Anglia, on ALEPH-NA:]

    This is an SQL I use monthly for some of our smaller members to report additions to stock; by removing the date range, and adding other factors where necessary, it could be modified to count current stock. The inclusion of z30_material is useful to exclude any items for serials issues.

    In all cases, the 'group by' is followed by an 'order by' because of the requirements of Oracle 10; if you are on an earlier version, you just need the 'group by'.

    set pause off pages 100

    col a format a6 heading 'Material'
    col b format a7 heading 'Coll'
    col c format 99999 heading 'Added'

    break on report on a skip 1
    compute sum of c on a report

    select
    Z30_MATERIAL a,
    Z30_COLLECTION b,
    count(*) c
    from z30
    where Z30_OPEN_DATE >= '&datefrom'
    group by
    Z30_MATERIAL,
    Z30_COLLECTION
    order by
    Z30_MATERIAL,
    Z30_COLLECTION
    /


    • Article last edited: 10/8/2013
    • Was this article helpful?
    //doorbell.io feedback widged