Finding Bibs with ONLY Lost and Missing Items

Clearinghouse Repository Polaris Repository
1

This query identifies bibliographic records that meet two specific conditions:

  1. They have at least one item record. This ensures you are looking at items that are part of your active collection, not titles where all items might have been deleted.

  2. All of their active item records are either “Lost” or “Missing.” This is the key part of the query. It uses a NOT EXISTS clause to filter out any bibliographic record that has an active item with a status other than “Lost” or “Missing”.

How the Script Works

The script uses a temporary table variable, @StatusList, to hold the item statuses you want to check for. The query then uses two EXISTS clauses to filter the bibliographic records.

-- Declare a TABLE variable to hold the status descriptions you want to check.
DECLARE @StatusList TABLE (
    Description NVARCHAR(100)
);

-- Populate the variable with the descriptions from the polaris.polaris.ItemStatuses table
-- You can easily add more here, like 'On-Order' or 'In-Repair'.
INSERT INTO @StatusList (Description)
VALUES ('Lost'), ('Missing');

-- Main query
SELECT
    br.BibliographicRecordID,
    br.BrowseTitle
FROM
    polaris.polaris.BibliographicRecords AS br
WHERE
    -- Check that at least one ACTIVE item exists for the bib.
    EXISTS (
        SELECT 1
        FROM polaris.polaris.CircItemRecords cir_check
        WHERE cir_check.AssociatedBibRecordID = br.BibliographicRecordID
          AND cir_check.RecordStatusID = 1  -- make sure all item records are final status
    )
    -- Now, check that NO ACTIVE items exist that are NOT in our @StatusList.
    AND NOT EXISTS (
        SELECT 1
        FROM polaris.polaris.CircItemRecords AS cir
        INNER JOIN polaris.polaris.ItemStatuses AS istatus
            ON cir.ItemStatusID = istatus.ItemStatusID
        WHERE
            cir.AssociatedBibRecordID = br.BibliographicRecordID
            AND cir.RecordStatusID = 1 -- make sure all item records are final status
            AND istatus.Description NOT IN (SELECT Description FROM @StatusList)
    );