It's that time of year again for annual reports. Some of the numbers I'm asked to dig up aren't easily available. So, I'm writing up this short guide to make it easier for you.
I was recently asked for the number of unique books from our main stacks that have circulated for the past fiscal year. We use Voyager as our ILS and there are no reports that include such a value. I decided that I would have to write my own SQL query to dig it up. I will be using SQL*Plus, which is the command line tool for accessing the Oracle database. Please note that the SQL provided is not compatible with MS Access (traditional means of generating reports from Voyager). If you intend to use MS Access you will need to modify the SQL as necessary.
You can run SQL*Plus by typing sqlplus into the command line. You will be prompted for a username and password. Once logged in, you will see a SQL> prompt. From there you can enter any SQL statement you like. If you have a collection of SQL statements in a file, you can execute them with @file where file is the name of a .sql file in your working directory (e.g. itemcounts.sql can be executed with @itemcounts). The default settings for SQL*Plus are difficult to work with. I advise issuing the following commands once logged in.
set linesize 150
set pagesize 50
If you don't want to type this in each time you use SQL*Plus, you can add them to $ORACLE_HOME/sqlplus/admin/glogin.sql. This file may be in a different location depending on your operating system and version of Oracle (I am using SunOS 5. 9 and Oracle 10g).
The SQL statement will prompt you for a start date, end date, item type and location code. The dates should be given in the format YYYYMMDD (i.e. May 28, 2009 would be 20090528). If you have trouble determining a particular code, you can view them all in the Voyager System Administration module under System -> Locations or System -> Item Types.
Circulation transactions are spread out between two tables: Circ_Transactions and Circ_Trans_Archive. This query finds the count of distinct item_id's from each table given an item type and location. The results are then combined by a union and summed to provide the final result.
SELECT SUM(count) FROM
-- Circ_Transactions
(SELECT Count(Distinct(t.Item_ID)) count
FROM Circ_Transactions t, Item i
WHERE t.Item_ID = i.Item_ID AND
-- Start Date
t.Charge_Date >= to_date('&&START_DATE_YYYYMMDD 00:00:00', 'YYYYMMDD HH24:MI:SS') AND
-- End Date
t.Charge_Date <= to_date('&&END_DATE_YYYYMMDD 23:59:59', 'YYYYMMDD HH24:MI:SS') AND
-- Item Type. Subquery converts given code to id.
((i.Temp_Item_Type_ID = (SELECT Item_Type_ID FROM Item_Type WHERE upper(Item_Type_Code) = upper('&&ITEM_TYPE_CODE'))) OR (i.Temp_Item_Type_ID = 0 AND i.Ite
m_Type_ID = (SELECT Item_Type_ID FROM Item_Type WHERE upper(Item_Type_Code) = upper('&&ITEM_TYPE_CODE')))) AND -- Item Type
-- Location. Subquery converts given code to id.
((i.Temp_Location = (SELECT Location_ID FROM Location WHERE upper(Location_Code) = upper('&&LOCATION_CODE'))) OR
(i.Temp_Location = 0 AND i.Perm_Location = (SELECT Location_ID FROM location WHERE upper(Location_Code) = upper('&&LOCATION_CODE'))))
UNION
-- Circ_Trans_Archive
SELECT Count(Distinct(t.Item_ID)) count
FROM Circ_Trans_Archive t, Item i
WHERE t.Item_ID = i.Item_ID AND
-- Start Date
t.Charge_Date >= to_date('&&START_DATE_YYYYMMDD 00:00:00', 'YYYYMMDD HH24:MI:SS') AND
-- End Date
t.Charge_Date <= to_date('&&END_DATE_YYYYMMDD 23:59:59', 'YYYYMMDD HH24:MI:SS') AND
-- Item Type. Subquery converts given code to id.
((i.Temp_Item_Type_ID = (SELECT Item_Type_ID FROM Item_Type WHERE upper(Item_Type_Code) = upper('&&ITEM_TYPE_CODE'))) OR (i.Temp_Item_Type_ID = 0 AND i.Ite
m_Type_ID = (SELECT Item_Type_ID FROM Item_Type WHERE upper(Item_Type_Code) = upper('&&ITEM_TYPE_CODE')))) AND -- Item Type
-- Location. Subquery converts given code to id.
((i.Temp_Location = (SELECT Location_ID FROM Location WHERE upper(Location_Code) = upper('&&LOCATION_CODE'))) OR
(i.Temp_Location = 0 AND i.Perm_Location = (SELECT Location_ID FROM location WHERE upper(Location_Code) = upper('&&LOCATION_CODE')))));
UNDEF LOCATION_CODE;
UNDEF ITEM_TYPE_CODE;
UNDEF START_DATE_YYYYMMDD;
UNDEF END_DATE_YYYYMMDD;
Cheers!