Finding unprocessed 970 tags in Polaris

Polaris
1

Use this SQL in the bib Find Tool to locate bib records that have 970 tags that are lacking a subfield $9 which indicates they haven’t been processed. This typically indicates the acquisitions process wasn’t completed properly. You may want to bulk delete these 970s after checking to make sure they won’t be needed.

This SQL only shows bibs that were last modified MORE than 3 days ago, you can adjust the days in the SQL to meet your needs.

select distinct br.BibliographicRecordID
from polaris.polaris.BibliographicRecords br
join polaris.polaris.BibliographicTags bt_970
    on bt_970.BibliographicRecordID = br.BibliographicRecordID and bt_970.TagNumber = 970
where not exists ( 
    select 1
    from polaris.polaris.BibliographicSubfields bs_9709
    where bs_9709.BibliographicTagID = bt_970.BibliographicTagID
        and bs_9709.subfield = '9'
)
    and br.ModificationDate < dateadd(day, -3, getdate()) --adjust days as needed
    and br.RecordStatusID = 1

Thanks to @ahoffman for this idea and the initial code from the Jan. 2023 Acq. forum and for @mfields for tidying up the code a bit. This version removes the need for the field to have a 970 $l (ell) that was present in the original code so it will find more instances of incomplete 970s than what was shared during the Zoom forum.