Get Hold Stats for Collection Code and/or Material Type
This query is useful to see which collections or material types are getting the most holds. You can modify as needed to search for only collections, only material types, both, or a combination.
/* --------------------------------------------------------------------------------------------------- */
/* PULL STATS FOR HOLDS PLACED ON ITEMS AT A GIVEN LIBRARY - LIMIT BY COLLECTION AND/OR MATERIAL TYPE. */
/* --------------------------------------------------------------------------------------------------- */
SELECT
o.Name AS "Branch/Library",
COUNT(DISTINCT th.TransactionID) AS "Hold Count"
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)
WHERE
th.TransactionTypeID = 6005 -- Hold Created
AND
th.TranClientDate between '2020-07-01 00:00:00.000' and '2020-07-31 23:59:59.999' -- Adjust dates as needed.
AND
th.OrganizationID in (14,15,110,113) -- Limit by organization IDs. This should match the organization IDs below.
AND
td.TransactionSubTypeID = 36 -- Look for BibliographicRecordID in the TransactionDetails table.
AND
td.numValue in (
-- Limit to a given material type and/or collection.
SELECT
DISTINCT ir.AssociatedBibRecordID
FROM
Polaris.Polaris.ItemRecords ir WITH (NOLOCK)
JOIN
Polaris.Polaris.BibliographicRecords br WITH (NOLOCK) ON br.BibliographicRecordID = ir.AssociatedBibRecordID
WHERE
OwningBranchID IN (14,15,110,113) -- Limit by organization IDs. This should match the organization IDs above.
AND
MaterialTypeID = 133 -- Set for a specific material type.
AND
AssignedCollectionID = 467 -- Set for a specific collection
)
GROUP BY
o.Name
ORDER BY
o.Name