• 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.

Procedure:
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

spool circ_count.lst

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)
having count(*)>4
order by count(*) desc;

spool off