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