- 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
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)
order by count(*) desc;
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 .
- Article last edited: 10/8/2013