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