Get Hold Stats for Collection Code and/or Material Type

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