Data Tool

Libraries Count is currently working with a programmer to provide a downloadable database for use in individual libraries. Please check back for any updates.

In the meantime, here are a list of queries that we use to help us collect library use data. Please feel free to adapt and use at your own institution.

CORE QUERIES FOR DATABASE TOOL

Patron Information

SELECT PATRON.INSTITUTION_ID AS PAT_INSTID, PATRON_BARCODE.PATRON_BARCODE AS PAT_BARCODE, PATRON_GROUP.PATRON_GROUP_NAME AS PAT_GROUP, PATRON_BARCODE_STATUS.BARCODE_STATUS_DESC AS PAT_STATUS, PATRON.CREATE_DATE AS PAT_CREATEDATE, PATRON.MODIFY_DATE AS PAT_MODIFYDATE, PATRON_NOTES.NOTE AS PAT_DEPARTMENT, PATRON_STATS.PATRON_STAT_ID, PATRON_STAT_CODE.PATRON_STAT_DESC FROM ((((PATRON_GROUP INNER JOIN (PATRON INNER JOIN PATRON_BARCODE ON PATRON.PATRON_ID = PATRON_BARCODE.PATRON_ID) ON PATRON_GROUP.PATRON_GROUP_ID = PATRON_BARCODE.PATRON_GROUP_ID) INNER JOIN PATRON_BARCODE_STATUS ON PATRON_BARCODE.BARCODE_STATUS = PATRON_BARCODE_STATUS.BARCODE_STATUS_TYPE) INNER JOIN PATRON_NOTES ON PATRON.PATRON_ID = PATRON_NOTES.PATRON_ID) INNER JOIN PATRON_STATS ON PATRON.PATRON_ID = PATRON_STATS.PATRON_ID) INNER JOIN PATRON_STAT_CODE ON PATRON_STATS.PATRON_STAT_ID = PATRON_STAT_CODE.PATRON_STAT_ID WHERE (((PATRON_GROUP.PATRON_GROUP_NAME) Not Like "UB*") AND ((PATRON_BARCODE_STATUS.BARCODE_STATUS_DESC)="Active"));

Patron Circulation Counter Snapshot (taken weekly)

SELECT Date() AS CNTR_RUNDATE, PATRON.INSTITUTION_ID AS PAT_INSTID, PATRON.HISTORICAL_CHARGES AS CNTR_HISTORICAL, PATRON.CURRENT_CHARGES AS CNTR_CURRENT, PATRON.CURRENT_CHARGES_UB AS CNTR_UBCURRENT, PATRON.HISTORICAL_CHARGES_UB AS CNTR_UBHISTORICAL FROM (PATRON_GROUP INNER JOIN ((PATRON INNER JOIN PATRON_NOTES ON PATRON.PATRON_ID = PATRON_NOTES.PATRON_ID) INNER JOIN PATRON_BARCODE ON PATRON.PATRON_ID = PATRON_BARCODE.PATRON_ID) ON PATRON_GROUP.PATRON_GROUP_ID = PATRON_BARCODE.PATRON_GROUP_ID) INNER JOIN NOTE_TYPE ON PATRON_NOTES.NOTE_TYPE = NOTE_TYPE.NOTE_TYPE WHERE (((PATRON_GROUP.PATRON_GROUP_NAME) Not Like "UB*")) GROUP BY PATRON.INSTITUTION_ID, PATRON.HISTORICAL_CHARGES, PATRON.CURRENT_CHARGES, PATRON.CURRENT_CHARGES_UB, PATRON.HISTORICAL_CHARGES_UB;

I run the same for students who have a book from the stacks checked out. It looks like this:

SELECT DISTINCT CIRC_TRANSACTIONS.PATRON_ID, PATRON.INSTITUTION_ID FROM (((((CIRC_TRANSACTIONS INNER JOIN PATRON_GROUP ON CIRC_TRANSACTIONS.PATRON_GROUP_ID = PATRON_GROUP.PATRON_GROUP_ID) INNER JOIN ITEM ON CIRC_TRANSACTIONS.ITEM_ID = ITEM.ITEM_ID) INNER JOIN ITEM_TYPE ON ITEM.ITEM_TYPE_ID = ITEM_TYPE.ITEM_TYPE_ID) INNER JOIN LOCATION ON ITEM.PERM_LOCATION = LOCATION.LOCATION_ID) INNER JOIN ITEM_STATUS ON ITEM.ITEM_ID = ITEM_STATUS.ITEM_ID) INNER JOIN PATRON ON CIRC_TRANSACTIONS.PATRON_ID = PATRON.PATRON_ID WHERE (((PATRON_GROUP.PATRON_GROUP_CODE)=”UG”) AND ((LOCATION.LOCATION_CODE)=”stacks”) AND ((ITEM_STATUS.ITEM_STATUS)=”2″));

Would love to get some feedback? If it can be done easier that would be awesome. Thanks.

So, every day (if at all possible) I run this Circ Snapshot – New Which gives me a list of all students that currently have a new item charged at the time I run the query

SELECT DISTINCT CIRC_TRANSACTIONS.PATRON_ID, PATRON.INSTITUTION_ID FROM (((((CIRC_TRANSACTIONS INNER JOIN PATRON_GROUP ON CIRC_TRANSACTIONS.PATRON_GROUP_ID = PATRON_GROUP.PATRON_GROUP_ID) INNER JOIN ITEM ON CIRC_TRANSACTIONS.ITEM_ID = ITEM.ITEM_ID) INNER JOIN ITEM_TYPE ON ITEM.ITEM_TYPE_ID = ITEM_TYPE.ITEM_TYPE_ID) INNER JOIN LOCATION ON ITEM.PERM_LOCATION = LOCATION.LOCATION_ID) INNER JOIN ITEM_STATUS ON ITEM.ITEM_ID = ITEM_STATUS.ITEM_ID) INNER JOIN PATRON ON CIRC_TRANSACTIONS.PATRON_ID = PATRON.PATRON_ID WHERE (((PATRON_GROUP.PATRON_GROUP_CODE)=”UG”) AND ((LOCATION.LOCATION_CODE)=”new”) AND ((ITEM_STATUS.ITEM_STATUS)=”2″));

I then take those IDs copy and paste them into an Excel spread sheet and de-duplicate for each semester.