Hold-to-Item Ratio SQL Query

Clearinghouse Repository Polaris Repository
1

When we switched from Sierra to Polaris in February we were surprised that Polaris doesn’t have a baked-in Hold-to-Item Ratio report like Decision Center did, so I worked with Innovative to write an SQL query that will tell my colleague and I (collections librarians) what items are in high demand and whether we need to order additional copies or not. It’s been very useful. I had Innovative adjust it a bit to remove records with no items attached and inactive holds, but I don’t know how to access our custom SQL report in SQL reporting, so I’m dropping the original query here for anyone else who wants or needs it.

DROP TABLE #Ratio

CREATE TABLE #Ratio
(
BibID int,
Hold_Count int,
Item_Count int
);

INSERT INTO #Ratio
SELECT 
shr.BibliographicRecordID,
COUNT(shr.BibliographicRecordID),
(SELECT COUNT(*) FROM 
Polaris.Polaris.CircItemRecords cir (nolock)
WHERE shr.BibliographicRecordID = cir.AssociatedBibRecordID
AND cir.ItemStatusID NOT IN (7, 10, 16, 20, 23))
FROM
Polaris.Polaris.SysHoldRequests shr (nolock)
GROUP BY shr.BibliographicRecordID


SELECT 
rat.BibID AS "Bib Record",
br.BrowseTitle AS "Title",
br.BrowseAuthor AS "Author",
br.BrowseCallNo AS "Call Number",
rat.Hold_Count AS "Total Holds",
rat.Item_Count AS "Total Items"
FROM #Ratio rat
JOIN Polaris.Polaris.BibliographicRecords br (nolock) ON rat.BibID = br.BibliographicRecordID
WHERE rat.Hold_Count / CASE WHEN rat.Item_Count = 0 THEN 1 ELSE rat.Item_Count END >= 5
1 Like
2

Thanks Sarah! This is a more elegant and detailed solution than what we use, which is a Bib List report, generated in Simply Reports and published to Reporting Services, which is subscribed to by librarians who then are sent it once/week.

That list is set for our criterion of active holds (3 or more) and number of items (1 or more), and generates an output of BibRecordID, BrowseAuthor, BrowseTitle, MARCTOMDescription, # of items and # of active holds. Of course we are just one library serving about 38K, so this is enough for us (the report rarely runs more than 3 pages long thank goodness). Also, we’re hosted so this is easier as only 2-3 of us have SQL query access and this can be a “set it and forget it” task. However, we’re always looking for good searches as some librarians would like more detail, so we appreciate you posting this!

2 Likes
3

Glad I could help, Kristen!