Report of all items updated in a particular time period

Description

We ran ret-adm-01 with Item Update Date filtering for 20180730 to 20180805. There are thousands of records which were changed (-- and which have an item history record for the update --) which are not included in the output. 

We need a report of all items updated in the last week with the item number, barcode, collection code, z30-CALL_NUMBER, copy no., description, title, 09x (call number from bib/z13), and HOL record number.

Resolution

As described in the article " What updates cause the z30_update_date to change? ", not all updates cause changes to the z30_update_date.  That's why the ret-01 output is incomplete.
 
Below is an “updateditems.sql” script for reporting on all items which have had a z30h history record created in a particular date range***  It would be submitted by doing “s+ xxx50”  and then:  @updateditems.sql

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 updateditems.lst

select z30_rec_key, z30_barcode, z30_collection, z30_call_no, z30_description, z13_title, z13_call_no, z30_hol_doc_number_x 
from z30, z30h, z103, xxx01.z13 where
  z30_rec_key = substr (z30h_rec_key,1,15) 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) and
  z30_sub_library = 'YYYYY' and
  Z30H_H_DATE >= '20180730' and Z30H_H_DATE <= '20180805';

spool off
exit

<end SQL script>

The  "XXX01"s need to be changed to your local bib library and the script needs to be submitted for xxx50 ("s+ xxx50").

By changing the Z30H_H_DATE line to this:

  Z30H_H_DATE > TO_CHAR (SYSDATE-7,'YYYYMMDD'); 

you could make the script always get the past 7 days.  (Thus, it could be run weekly without changing the script.)

If wanted to do it for all sublibraries, you could delete the z30_sub_library line.  In this case you may want to add an “order by z30_sub_library” line at the end of the where clause.

*** Note that not all item updates result in the creation of a z30h record.  See the article " No History records for certain items **MASTER RECORD** " in this regard. 

If you want put the output into a spreadsheet, you could insert a "SET MARKUP HTML ON SPOOL ON " line immediately before the "spool " line in the script and follow the instructions in the " How to put SQL output into a spreadsheet " article.

• Article last edited: 10-Aug-2018