- Article Type: General
- Product: Aleph
- Product Version: 20, 21, 22, 23
We need a report that lists what items have never been checked out (and are prime candidates to be weeded). Please explain the best way to do this.
You can use the p_ret_adm_01 General (ADM) Retrieval form to find items with No. of loans 0, but you will almost certainly want to limit the results to items which are *not* ISSUE items and items purchased before some date, say, 20170101.
The following SQL script can be used to do that (with additions such as "and z30_material ^= 'ISSUE' to the where clause):
set echo off
set pause off
set feed off
set heading off
SELECT /*+ DYNAMIC_SAMPLING(2) ALL_ROWS */ z30_collection, z30_call_no, Z30_BARCODE, Z13_TITLE from ABC01.Z13, Z30, Z103 where Z13_REC_KEY = substr (Z103_REC_KEY_1,6,9) and substr (Z103_REC_KEY_1,1,5) = 'ABC01'
and substr (Z103_REC_KEY,6,9) = substr (Z30_REC_KEY,1,9) and z30_open_date < '20170101' and z30_sub_library = 'WO' and z30_no_loans = 0 order by z30_collection, z30_call_no;
See also the Article: SQL to locate items with fewer (or more) than x loans .
[From Alan Exelby, University of East Anglia, on Global Aleph Users list:]
A couple of people were interested in our SQLs, so, with apologies for the delay, here they are:
1. Fairly simple SQL listing items with the total ‘use count’ from z30:
from z13, z30
where z13_rec_key = substr(z30_rec_key,1,9)
and z30_sub_library like 'CR%'
and z30_item_process_status is null
order by Z30_CALL_NO,z13_title;
(This is a quick-and-dirty report primariy intended to allow our short-loan section to identify un-used items and assess whether to send them back to normal stock.)
2. More advanced SQL counting z36H records by Item for a specific period and reporting those with a total higher than a specified number:
SELECT z30_call_no||'\'||COUNT (z36h_loan_date)||'\'||
z36h_rec_key||'\'||SUBSTR (z13_title, 1, 50)||'\'||z30_barcode
FROM z36h, z30, z13
WHERE z30_rec_key = z36h_rec_key
AND SUBSTR (z30_rec_key, 1, 9) = z13_rec_key
AND z36h_loan_date >= '20100101'
AND z36h_loan_date IS NOT NULL
AND z30_item_process_status IS NULL
GROUP BY z30_call_no, z36h_rec_key, SUBSTR (z13_title, 1, 50), z30_barcode
HAVING COUNT (z36h_loan_date) >= 4
ORDER BY z30_call_no, z36h_rec_key
(This is for a report on books that have been heavily-used recently, designed for our Faculty Librarians as one of the tools on which they base decisions on buying extra copies or electronic editions.)
3. Similar to no.2 above but supposedly counting z36H records together for a title-level count rather than Item-level:
set pause off head off
set lines 200
(select count(z30_barcode) as qty, z30_call_no as call_no, z13_title as title
from z36h, z30, z13
where z30_item_process_status is null and
substr(z30_rec_key, 1,9) = substr(z36H_rec_key, 1,9) and
substr(z36H_rec_key, 1,9) = substr(z13_rec_key, 1,9) and
z36H_loan_date >= '20100101' and
z30_barcode = z30_barcode and
z36H_loan_date is not null
group by z30_barcode, z13_title, z30_call_no
order by z30_barcode, z13_title, z30_call_no
where qty >=4
This third option was developed before no.2, and not by me; I was never convinced the results were 100% reliable (comparison of results with figures in the GUI suggested ca. 95%), but our Faculty Librarians, who had requested the report in the first place, changed their minds and decided they wanted it by item-level anyway, so we developed no.2 above and abandoned this one; it has not been used in several years.
General notes re. these three SQLs:
- we run SQLs with a delimiter and generally expect the recipients to use that delimiter for conversion to Excel; the delimiter we normally use is the left-slash (\), which we find is the only basic keyboard character that does not also occur in the data – many years ago, we tried to get a tab inserted as a delimiter, but even very experienced SQL-ers could not get the normal inputs to work, so we gave up and standardised on the left slash. If you prefer another delimiter, just use it instead of the left slash.
- the versions above are base-line versions and are normally used with fine-tuning of the ‘where’ clauses as needed
[From Frank Vandepitte <Frank.Vandepitte@UGENT.BE>:]
Another way would be to have an html table output, which is readable and can also easily be imported in excel
Almost all of our reporting is done in that way
(result : http://aleph.ugent.be/adhoc/test.html)
SET PAGESIZE 50000
SET PAUSE OFF
SET FEEDBACK OFF
SET TERMOUT OFF
SET MARKUP HTML ON SPOOL ON
COLUMN SYSTIME HEADING "JUST TESTING ;-)" FORMAT A100
SELECT TO_CHAR(SYSDATE,'DD-MON-RRRR HH24:MI:SS') SYSTIME FROM DUAL;
Z30_CALL_NO CN, Z13_TITLE TI, Z30_NO_LOANS NR, Z30_DATE_LAST_RETURN RT, Z30_BARCODE BC
Z13_REC_KEY = SUBSTR(Z30_REC_KEY,1,9)
AND Z30_SUB_LIBRARY LIKE 'PS01%'
AND Z30_ITEM_PROCESS_STATUS IS NULL
ORDER BY Z30_CALL_NO,Z13_TITLE;
<end Frank Vandepitte>
See also Article 000036070 SQL to locate items with fewer (or more) than x loans
- Article last edited: 10/23/2014