Description

We would like a list of titles that have been checked out, showing how many times each has been checked out.

Resolution

The following, using the z30_loan_no column, shows how many times each item has been checked out. (Change each "xxx50" to your ADM library and each "xxx01" to your bib library.)

s+ xxx50;
SQL-XXX50> SELECT /*+ DYNAMIC_SAMPLING(2) ALL_ROWS */ Z13_TITLE, z30_rec_key, z30_rec_key, z30_no_loans  from xxx01.Z13, Z30, Z103 where z30_no_loans ^= '0' and Z13_REC_KEY = substr (Z103_REC_KEY_1,6,9) and substr (Z103_REC_KEY_1,1,5) = 'xxx01' and substr (Z103_REC_KEY,6,9) = substr (Z30_REC_KEY,1,9) order by z30_no_loans desc;

Note that the z30_no_loans has a maximum of "999".  See the article " Does Aleph have a limit on the number of loans that show on the item record? "  in this regard.

**More**

Below is a "loan-count.sql" report which was created for a particular case.  It is based on a call number range.

set echo off
set pause off
set term off
set show off
set feed off
set verify off
set heading off
set pagesize 58

spool loan-count.lst

SELECT /*+ DYNAMIC_SAMPLING(2) ALL_ROWS */ z30_no_loans from xxx01.Z13, Z30, Z103 where
Z13_REC_KEY = substr (Z103_REC_KEY_1,6,9) and
substr (Z103_REC_KEY_1,1,5) = 'xxx01' and
substr (Z103_REC_KEY,6,9) = substr (Z30_REC_KEY,1,9) and
z30_collection = 'XXXXX' and
z30_call_no like '$$hTR%'
order by z30_call_no asc;

spool off
exit

<end loan-count.sql report>

In this case, we included all call numbers beginning with $$hTR%. If you want to exclude items which have zero loans, you could add the following to the "where" clause: 

 and z30_no_loans ^= '0' 

You could change the "order by" to z30_barcode, "count (*)", or any other field in the "SELECT". 

To execute the script you do: 

> s+ xxx50 
xxx50@ALEPH21> @loan-count.sql 

• Article last edited: 14-Oct-2018