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).

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