Find Bib Records With Active Holds But No Items

Clearinghouse Repository Polaris Repository
,
1

Find Bib Records With Active Holds But No Items

This query finds Bib Records with active holds that do not have items (excluding on order). It then puts the results into a record set.

Contributed by: Derek Brown - Rochester Hills Public Library

-- Youth Reports - Holds on Bibs w/o Items
Delete from
    polaris.polaris.BibRecordSets
WHERE
    RecordSetID in (1234) -- Insert your Bibliographic RecordSetID here. This will clear previous records in the record set.
Select
    distinct PO.BibliographicRecordID as BibrecordID into #tempdb -- Import data into temp before moving to recordset
from
    polaris.polaris.POLines as PO with (nolock)
join
    polaris.polaris.POLineItemSegments as POLIS with (nolock) on PO.POLineItemID = POLIS.POLineItemID
where
    POLIS.DestinationCollectionID in () -- Insert CollectionIDs here to limit to Youth Collections
-- Find Bibs Missing Items
and
    PO.BibliographicRecordID not in (
        (SELECT
            DISTINCT(AssociatedBibRecordID)
        FROM
            Polaris.Polaris.CircItemRecords WITH (NOLOCK))
        )
-- Exclude Bibs On-Order Missing Items
and
    PO.bibliographicrecordid not in (
    select
        BR.BibliographicRecordID 
    from
        polaris.polaris.BibliographicRecords as BR with (nolock) 
    join
        Polaris.polaris.POLines PO on (BR.BibliographicRecordID=PO.BibliographicRecordID)
    join
        polaris.polaris.POLineItemSegments POLIS on (POLIS.POLineItemID=PO.POLineItemID) 
    where
        POLIS.StatusID=12
)

insert into polaris.polaris.BibRecordSets (RecordSetID, BibliographicRecordID)
select 1234, BibrecordID from #tempdb -- Insert into RecordSet (Change the first number in the statement to match your RecordSetID above)

drop table #tempdb
2

If you don’t use acquisitions or just want to find all titles with no items but holds, you can use this SQL. This totals the request but also breaks them down by request type.

Because it is not looping in the Purchase order information, there are some limitations:

  • You cannot limit by collection code in this script.
  • This script truly looks only for bibs where there are NO attached items. If you retain deleted items, those deleted items would cause the title NOT to appear on this script because it is an attached record.
SELECT
    br.BibliographicRecordID,
    br.BrowseTitle AS Title,
    br.BrowseAuthor AS Author,
    br.BrowseCallNo AS "Call Number",
    COUNT(shr.BibliographicRecordID) AS Total_Holds,
    SUM(CASE WHEN shs.Description = 'Requested' THEN 1 ELSE 0 END) AS Holds_Requested,
    SUM(CASE WHEN shs.Description = 'Active' THEN 1 ELSE 0 END) AS Holds_Active,
    SUM(CASE WHEN shs.Description = 'Shipped' THEN 1 ELSE 0 END) AS Holds_Shipped,
    SUM(CASE WHEN shs.Description = 'Held' THEN 1 ELSE 0 END) AS Holds_Held,
    SUM(CASE WHEN shs.Description = 'Not Supplied' THEN 1 ELSE 0 END) AS Holds_Not_Supplied,
    SUM(CASE WHEN shs.Description = 'Unclaimed' THEN 1 ELSE 0 END) AS Holds_Unclaimed,
    SUM(CASE WHEN shs.Description = 'Request Expired' THEN 1 ELSE 0 END) AS Holds_Request_Expired,
    SUM(CASE WHEN shs.Description = 'Cancelled' THEN 1 ELSE 0 END) AS Holds_Cancelled,
    SUM(CASE WHEN shs.Description = 'Out' THEN 1 ELSE 0 END) AS Holds_Out,
    SUM(CASE WHEN shs.Description = 'Located' THEN 1 ELSE 0 END) AS Holds_Located,
    SUM(CASE WHEN shs.Description = 'Suspended' THEN 1 ELSE 0 END) AS Holds_Suspended
FROM
    polaris.polaris.BibliographicRecords AS br
INNER JOIN
    polaris.polaris.SysHoldRequests AS shr
    ON br.BibliographicRecordID = shr.BibliographicRecordID
INNER JOIN
    polaris.polaris.SysHoldStatuses AS shs
    ON shr.SysHoldStatusID = shs.SysHoldStatusID
WHERE
    NOT EXISTS (
        SELECT 1
        FROM polaris.polaris.CircItemRecords AS cir
        WHERE cir.AssociatedBibRecordID = br.BibliographicRecordID
    )
GROUP BY
    br.BibliographicRecordID,
    br.BrowseTitle,
    br.BrowseAuthor,
    br.BrowseCallNo
ORDER BY
    br.BibliographicRecordID;