Find who placed recent holds for patron over a certain limit

Clearinghouse Repository Polaris Repository
1

Vibe coded from a Discord request, so all the usual caveats apply. This allows you to see who might be “overriding” hold request limits.

If you wanted to get fancy, you could wrap in looking up in the Sys Admin tables the holds limits settings for each patron and org combination and then just report those rather than using a fixed MinHoldCount.

-- Set your criteria here
DECLARE @MinHoldCount INT = 50;
DECLARE @RecentHoldsToShow INT = 3;

-- This CTE identifies patrons with a number of active holds greater than or equal to @MinHoldCount
WITH HighHoldPatrons AS (
    SELECT
        shr.PatronID
    FROM
        polaris.polaris.SysHoldRequests shr WITH (NOLOCK)
    WHERE
        shr.SysHoldStatusID IN (3, 4, 5, 6, 18) -- Active, Pending, Shipped, Held, Located
    GROUP BY
        shr.PatronID
    HAVING
        COUNT(shr.SysHoldRequestID) >= @MinHoldCount
),
RankedHolds AS (
    -- This CTE ranks all hold requests for the identified patrons by creation date (newest first)
    SELECT
        shr.PatronID,
        shr.SysHoldRequestID,
        shr.CreationDate,
        shr.CreatorID,
        shr.BibliographicRecordID,
        ROW_NUMBER() OVER(PARTITION BY shr.PatronID ORDER BY shr.CreationDate DESC) AS rn
    FROM
        polaris.polaris.SysHoldRequests shr WITH (NOLOCK)
    WHERE
        shr.PatronID IN (SELECT PatronID FROM HighHoldPatrons)
)
-- This final query selects the top N most recent holds and joins to other tables to get descriptive information
SELECT
    p.PatronID,
    p.Barcode AS PatronBarcode,
    pr.PatronFirstLastName AS PatronName,
    rh.CreationDate AS HoldRequestDate,
    rh.SysHoldRequestID,
    br.BibliographicRecordID,
    br.BrowseTitle,
    pu.PolarisUserID AS PlacedByUserID,
    pu.Name AS PlacedByUserName
FROM
    RankedHolds rh
JOIN
    polaris.polaris.Patrons p WITH (NOLOCK) ON rh.PatronID = p.PatronID
JOIN
    polaris.polaris.PatronRegistration pr WITH (NOLOCK) ON p.PatronID = pr.PatronID
JOIN
    polaris.polaris.PolarisUsers pu WITH (NOLOCK) ON rh.CreatorID = pu.PolarisUserID
JOIN
    polaris.polaris.BibliographicRecords br WITH (NOLOCK) ON rh.BibliographicRecordID = br.BibliographicRecordID
WHERE
    rh.rn <= @RecentHoldsToShow
ORDER BY
    p.PatronID,
    rh.CreationDate DESC;
1 Like