• 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