- Article Type: General
- Product: Aleph
- Product Version: 18
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.
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 (firstname.lastname@example.org) 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
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'
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
[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
where Z30_OPEN_DATE >= '&datefrom'
- Article last edited: 10/8/2013