Hold Modifications by Staff

Clearinghouse Repository Polaris Repository
1

This query pulls a list of holds modifications over a given period of time and provides data about who made the change, at which branch, in which subsystem, along with bibliographic and patron info.

SELECT
	th.TranClientDate AS [Transaction Date],
	tt.TransactionTypeDescription AS [Transaction],
	subsystem.TransactionSubTypeCodeDesc AS [Subsystem],
	holdreqid.numValue AS [SysHoldRequestID],
	p.Barcode AS [Patron Barcode],
	bibid.numValue AS [BibliographicRecordID],
	br.BrowseTitle AS [Title],
	br.BrowseAuthor AS [Author],
	pu.Name [Polaris User],
	o.DisplayName [Branch/Library]
FROM
	PolarisTransactions.Polaris.TransactionHeaders th
INNER JOIN -- TransactionType labels
	PolarisTransactions.Polaris.TransactionTypes tt
	ON (tt.TransactionTypeID = th.TransactionTypeID)
INNER JOIN -- Gets the PatronID
	PolarisTransactions.Polaris.TransactionDetails patron
	ON (th.TransactionID = patron.TransactionID AND patron.TransactionSubTypeID = 6)
INNER JOIN -- Pulls SysHoldRequestID
	PolarisTransactions.Polaris.TransactionDetails holdreqid
	ON (th.TransactionID = holdreqid.TransactionID AND holdreqid.TransactionSubTypeID = 233)
INNER JOIN -- Pulls BibliographicRecordID
	PolarisTransactions.Polaris.TransactionDetails bibid
	ON (th.TransactionID = bibid.TransactionID AND bibid.TransactionSubTypeID = 36)
INNER JOIN -- Pulls Subsystem Type
	PolarisTransactions.Polaris.TransactionDetails subtype
	on (th.TransactionID = subtype.TransactionID AND subtype.TransactionSubTypeID = 235)
INNER JOIN -- Pulls the subsytem description
	PolarisTransactions.Polaris.TransactionSubTypeCodes subsystem
	ON (subtype.numValue = subsystem.TransactionSubTypeCode AND subsystem.TransactionSubTypeID = 235)
INNER JOIN -- Get Polaris user name
	Polaris.Polaris.PolarisUsers pu
	ON (pu.PolarisUserID = th.PolarisUserID)
INNER JOIN -- Get the library or branch info
	Polaris.Polaris.Organizations o
	ON (o.OrganizationID = th.OrganizationID)
INNER JOIN -- Get info from bib records
	Polaris.Polaris.BibliographicRecords br
	ON (br.BibliographicRecordID = bibid.numValue)
INNER JOIN -- Get the patron barcode
	Polaris.Polaris.Patrons p
	ON (p.PatronID = patron.numValue)
WHERE -- Hold modifications and cancellations
	th.TransactionTypeID IN (6013,6057,6009,6051,6052,6053,6057,6058)
AND -- Adjust dates as desired
	th.TranClientDate >= '2025-10-20'
AND
	th.TranClientDate <= '2025-10-21 23:59:59.999'
AND -- Eliminate system level activity
	th.PolarisUserID NOT IN (1, 2, 3)
2 Likes