• 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