Collection Health Holds.sql (51.1 KB)
(latest version uploaded 7/24/2025)
This “Collection Health Holds Query” has been in development for over a year. I first mentioned it in this post and presented a snippet of it at IUG 2024.
Using the filled hold and checkout transactions in circ_trans, output an analysis of checkouts by branch for the [previous] reporting month:
A. Reporting month (or first month of the reporting period if adjusted)
B. Display order
C. Branch/Collection/Pickup Location Name
D. Count of holds checked out
E. Count of browsed items checked out
F. Ratio of D:E
G. Count of holds filled from the local collection
H. Count of holds transited in from another branch
I. Ratio of G:H ± an estimated margin of error calculated as J÷E
J. Count of browsed checkouts having a non-local branch location code prefix
K. Count of inbound INN-Reach items
L. Count of outbound INN-Reach items
M. Ratio of K:L
N. Count of patron visits having checkouts
O. Checkouts per visit, calculated as (D+E)/N
P. Coincidental Checkouts (see Wes’ July 10 suggestion in this thread)
Most useful if your circ_trans table is configured to retain at least 32 days of history; ours is configured to retain 1,110 days or 36½ months. You could run it weekly, but the smaller sample size will be more sensitive to spikes in circ activity, closed holidays, unscheduled branch closures, etc. The ratios are expressed as percentages of a total.
Customization will of course be required for your site, primarily to map location codes and stat groups to branches. Note that this mapping is required in three places in the query batch (there are 30 SQL statements in here; table and index creation, inserts, updates and the final select for the report itself, plus two optional diagnostic queries).
In addition to producing insightful statistics, the code presents useful examples of:
- CTEs (Common Table Expressions)
- Date functions and date arithmetic using INTERVAL
- Regular Expression string matching
- LEFT OUTER JOIN used to find non-matching rows
- CROSS JOINs
- UNIONs
- INTERSECT
- INNER JOINing a table to itself (self-referential) due to a hierarchical row relationship
- Creation of Temporary Tables
- Building a Primary Key on a temp table
- Building Indexes on a temp table
- INSERTing data into temp tables
- UPDATE statements on temp tables
- The ROW_NUMBER() Window Function
- The LAG() Window Function
- Use of the LATERAL keyword (see the code comments for more about this)
- The GENERATE_SERIES() function
The concept of “visit” bears some explanation: Since the scope of this report is limited to checkout activity, so is the calculation of visits. Acknowledging the many possible reasons a patron visits a library branch, a visit in this report is purely defined by a patron checking out one or more items on a given day at a given branch, and if a long enough gap occurs between checkouts, this report considers that to be a separate visit. In the tmpReportParameters table, I have defined that gap as 90 minutes, but I may later extend that to account for patrons who pick up holds and/or browse before and after a program they’re attending. You are likewise encouraged to reconsider this parameter with your patron behavior in mind.
The visits calculation was only added because it is needed for the coincidental checkouts calculation, and the checkouts per visit column was only added because the data to produce it was available within this report. Checkouts per visit calculations based on door counter data will produce different results, particularly in the case of a parent showing up with four kids (5 door counts) and all of them are using the same library card to check out items.
When run for the default one-month reporting period, this query batch completes for me in 2-3 minutes. It naturally takes a wee bit longer if you’re running a YTD report, full year, or even all of circ_trans history… YMMV. Enjoy!