List of titles which have been checked out, showing number of loans
- Product: Aleph
- Product Version: 20, 21, 22, 23
- Relevant for Installation Type: Dedicated-Direct, Direct, Local, Total Care
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