SQL to find number of items in collections that were returned late
- Article Type: General
- Product: Aleph
- Product Version: 18.01
Description:
Is there a report or SQL to show the number of items in each collection that were returned late?
Ideally, it would show how many days late the items were returned.
Resolution:
The Description in the z31 cash record created for an overdue item looks like this:
z31_description ..............Late return <0004 0000 2 .10>
The first number is the number of days the item is overdue.
The following SQL script will give you a count of the number of instances for each number of days overdue, ordered by collection:
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 days_overdue_by_collection.lst
select z30_collection, substr (z31_description,14,4), count(*) from z30, z31 where substr (z31_key,1,15) = z30_rec_key and z31_description like 'Late return <%'
group by z30_collection, substr (z31_description,14,4)
order by z30_collection, substr (z31_description,14,4) asc;
spool off
Note: If you need *sublibrary* by collection, the following select can be used instead:
select z30_sub_library, z30_collection, substr (z31_description,14,4), count(*) from z30, z31 where substr (z31_key,1,15) = z30_rec_key and z31_description like 'Late return <%'
group by z30_sub_library, z30_collection, substr (z31_description,14,4)
order by z30_sub_library, z30_collection, substr (z31_description,14,4) asc;
- Article last edited: 10/8/2013