Active Holds Where Items are Checked In

Clearinghouse Repository Polaris Repository
,
1

Active Holds Where Items are Checked In

This query finds active holds that are sitting on shelf. Useful for finding items that fell of the RTF list. It gives you the owning and assigned branches to help you find the item in one place or another.

SELECT
    shr.BibliographicRecordID as "Bib ID",
    c.Name AS "Collection",
    ir.CallNumber AS "Call Number",
    br.BrowseAuthor AS "Author",
    br.BrowseTitle AS "Title",
    mat.Description AS "Format",
    o.Name AS "Owning Branch",
    o2.Name AS "Assigned Branch"

FROM
    Polaris.Polaris.SysHoldRequests shr WITH (NOLOCK)

INNER JOIN
    Polaris.Polaris.ItemRecords ir WITH (NOLOCK) ON ir.AssociatedBibRecordID = shr.BibliographicRecordID
INNER JOIN
    Polaris.Polaris.BibliographicRecords br WITH (NOLOCK) ON br.BibliographicRecordID = shr.BibliographicRecordID
INNER JOIN
    Polaris.Polaris.Collections c WITH (NOLOCK) ON c.CollectionID = ir.AssignedCollectionID
INNER JOIN
    Polaris.Polaris.MaterialTypes mat WITH (NOLOCK) ON mat.MaterialTypeID = ir.MaterialTypeID
INNER JOIN
    Polaris.Polaris.Organizations o WITH (NOLOCK) ON o.OrganizationID = ir.OwningBranchID
INNER JOIN
    Polaris.Polaris.Organizations o2 WITH (NOLOCK) on o2.OrganizationID = ir.AssignedBranchID

WHERE
    shr.SysHoldStatusID = 3

AND ir.ItemRecordID IN (
    SELECT
        ItemRecordID
    FROM
        Polaris.Polaris.ItemRecords WITH (NOLOCK)
    WHERE
        ItemStatusID IN (1,19)
        --AND OwningBranchID IN (106,107,108) -- ASSIGN ORG CODES FOR LIBRARIES/BRANCHES IF DESIRED
)

GROUP BY
    shr.BibliographicRecordID, 
    c.Name, 
    ir.CallNumber, 
    br.BrowseAuthor, 
    br.BrowseTitle, 
    mat.[Description], 
    o.Name, 
    o2.Name

ORDER BY 
    o.Name, 
    o2.Name, 
    c.Name, 
    ir.CallNumber