Hindered Holds

Clearinghouse Repository Polaris Repository
,
1

Hindered Holds

This query looks for holds with problems. Maybe the item isn’t supplied, or it’s a hold that’s several months old, or there are no items to fill the hold, and so on. It pulls in the item and hold information so you can track down these issues and, hopefully, solve them.

Note: There’s also an RDL file for this query in the repo. It can be opened in Microsoft Report Builder.

Big thanks to: Trevor Diamond - MAIN (NJ)

SELECT DISTINCT(shr.SysHoldRequestID), 'Not Supplied' AS [Reason]
INTO ##HinderedHolds
FROM Polaris.Polaris.SysHoldRequests AS [shr] WITH (NOLOCK)
--where hold is not supplied
WHERE shr.SysHoldStatusID = '7'

SET IDENTITY_INSERT ##HinderedHolds ON

INSERT INTO ##HinderedHolds (SysHoldRequestID, Reason)
SELECT DISTINCT(shr.SysHoldRequestID), 'Long Active'
FROM Polaris.Polaris.SysHoldRequests AS [shr] WITH (NOLOCK)
WHERE --hold older than 4 months
	shr.ActivationDate < DATEADD(month,-4,GETDATE())
--it isn't cancelled
AND shr.SysHoldStatusID NOT IN ('8','16','7')

INSERT INTO ##HinderedHolds (SysHoldRequestID, Reason)
SELECT DISTINCT(shr.SysHoldRequestID), 'Hold Shelf'
FROM Polaris.Polaris.SysHoldRequests AS [shr] WITH (NOLOCK)
WHERE shr.SysHoldStatusID = '8'
AND shr.HoldTillDate < DATEADD(day,-14,GETDATE())

--finding bib level holds where there are no "good" items on the bib record
INSERT INTO ##HinderedHolds (SysHoldRequestID, Reason)
SELECT DISTINCT(shr.SysHoldRequestID), 'No Items'
FROM Polaris.Polaris.SysHoldRequests AS [shr] WITH (NOLOCK)
WHERE ItemBarcode IS NULL
AND shr.BibliographicRecordID IN (SELECT DISTINCT(cir.AssociatedBibRecordID)
FROM Polaris.Polaris.CircItemRecords AS [cir] WITH (NOLOCK)
WHERE cir.ItemStatusID IN ('7','10','8','9','21','12','14','16')
AND cir.AssociatedBibRecordID NOT IN (SELECT DISTINCT(cir.AssociatedBibRecordID)
FROM Polaris.Polaris.CircItemRecords AS [cir] WITH (NOLOCK)
WHERE cir.ItemStatusID NOT IN ('7','10','8','9','21','12','14','16')))

--item level holds where the item is "bad"
INSERT INTO ##HinderedHolds (SysHoldRequestID, Reason)
SELECT DISTINCT(shr.SysHoldRequestID), 'No Items'
FROM Polaris.Polaris.SysHoldRequests AS [shr] WITH (NOLOCK)
INNER JOIN Polaris.Polaris.CircItemRecords AS [titleitem] WITH (NOLOCK)
ON shr.ItemBarcode = titleitem.Barcode
WHERE titleitem.ItemStatusID IN ('7','10','8','9','21','12','14','16')

--pull information on all the holds and put it in a report!
SELECT	DISTINCT(hh.SysHoldRequestID),
		o.Abbreviation AS [Library], 
		p.Barcode AS [Patron Barcode], 
		pr.PatronFullName,
		hh.Reason, 
		cir.Barcode AS [Item Barcode], 
		c.Name AS [Collection], 
		ird.CallNumberVolumeCopy AS [Call Number], 
		br2.BrowseAuthor AS [Author],
		br2.BrowseTitle AS [Title], 
		br2.PublicationYear AS [Publication Year], 
		cir.LastCheckOutRenewDate AS [Last Checkout], 
		itst.Description AS [Item Status], 
		cir.ItemStatusDate AS [Status Date]
FROM ##HinderedHolds AS [hh] WITH (NOLOCK)
INNER JOIN Polaris.Polaris.SysHoldRequests AS [shr] WITH (NOLOCK)
ON hh.SysHoldRequestID = shr.SysHoldRequestID
INNER JOIN Polaris.Polaris.Patrons AS [p] WITH (NOLOCK)
ON shr.PatronID = p.PatronID
INNER JOIN Polaris.Polaris.Organizations AS [o] WITH (NOLOCK)
ON p.OrganizationID = o.OrganizationID
LEFT OUTER JOIN Polaris.Polaris.CircItemRecords AS [cir] WITH (NOLOCK)
ON shr.ItemBarcode = cir.Barcode
LEFT OUTER JOIN Polaris.Polaris.BibliographicRecords AS [br] WITH (NOLOCK)
on cir.AssociatedBibRecordID = br.BibliographicRecordID
LEFT OUTER JOIN Polaris.Polaris.Collections AS [c] WITH (NOLOCK)
ON cir.AssignedCollectionID = c.CollectionID
LEFT OUTER JOIN Polaris.Polaris.ItemRecordDetails AS [ird] WITH (NOLOCK)
ON cir.ItemRecordID = ird.ItemRecordID
LEFT OUTER JOIN Polaris.Polaris.BibliographicRecords AS [br2] WITH (NOLOCK)
ON shr.BibliographicRecordID = br2.BibliographicRecordID
INNER JOIN Polaris.Polaris.PatronRegistration AS [pr] WITH (NOLOCK)
ON p.PatronID = pr.PatronID
LEFT OUTER JOIN Polaris.Polaris.ItemStatuses AS [itst] WITH (NOLOCK)
ON cir.ItemStatusID = itst.ItemStatusID
LEFT OUTER JOIN Polaris.Polaris.Organizations AS [io] WITH (NOLOCK)
ON cir.AssignedBranchID = io.OrganizationID
WHERE o.OrganizationID IN (@OrganizationID)
OR (hh.Reason LIKE 'Hold Shelf' AND io.OrganizationID IN (@OrganizationID))

--drop that damn table like a bass in dubstep
DROP TABLE ##HinderedHolds