Find Hold Information from Given Dates
Using this query, you can get detailed information about hold acvtivity for a given library/branch on a given range of dates. The codes for hold requests are noted at the bottom of the code.
SELECT
o.Name AS "Library/Branch",
pr.PatronID AS "Patron ID",
pr.PatronFullName AS "Patron Name",
td2.numValue AS "Bib Record ID",
br.BrowseTitle AS "Title",
tt.TransactionTypeDescription AS "Hold Action"
FROM
PolarisTransactions.Polaris.TransactionHeaders th WITH (NOLOCK)
INNER JOIN
PolarisTransactions.Polaris.TransactionDetails td WITH (NOLOCK) ON th.TransactionID = td.TransactionID
INNER JOIN
Polaris.Polaris.Organizations o WITH (NOLOCK) ON th.OrganizationID = o.OrganizationID
INNER JOIN
Polaris.Polaris.PatronRegistration pr WITH (NOLOCK) ON pr.PatronID = td.NumValue
INNER JOIN
PolarisTransactions.Polaris.TransactionDetails td2 WITH (NOLOCK) ON th.TransactionID = td2.TransactionID
LEFT OUTER JOIN
Polaris.Polaris.BibliographicRecords br WITH (NOLOCK) ON br.BibliographicRecordID = td2.numValue
INNER JOIN
PolarisTransactions.Polaris.TransactionTypes tt WITH (NOLOCK) on tt.TransactionTypeID = th.TransactionTypeID
WHERE
th.TransactionTypeID = 6013 -- Set this as needed. See list below for codes.
AND
td.TransactionSubTypeID = 6 -- Pulls the patron ID
AND
td2.TransactionSubTypeID = 36 -- Pulls the bib record ID
AND
th.TranClientDate BETWEEN '2021-04-01 00:00:00.000' AND '2021-04-01 23:59:59.999'
AND
th.OrganizationID IN (3,4) -- Adjust for your branche/library org codes
GROUP BY
o.Name,
pr.PatronID,
pr.PatronFullName,
td2.numValue,
br.BrowseTitle,
tt.TransactionTypeDescription
ORDER BY
o.Name
/*
Transaction Type IDs for Various Hold Actions
---------------------------------------------
6005 Hold request created
6006 Holds become held (item received for hold request)
6007 Holds become expired
6008 Holds become unclaimed
6009 Holds become denied
6010 Holds become pending
6011 Holds become not supplied
6012 Holds become shipped
6013 Holds become cancelled
*/