Script using SQL extracts bibliographic records with only one item attached

Sierra, Millennium & Encore
1

Hello fellow IUG members,

I’m reaching out to see if anyone has created an SQL script that extracts bibliographic records with only one item attached. We’re trying to identify and clean up some of our bibliographic records that only have a single item, and I thought I’d check to see if anyone has already tackled this task.

If you have a script you’d be willing to share, I’d greatly appreciate it! Alternatively, if you have any tips or suggestions on how to approach this task, I’m all ears.

Thanks in advance for your help!

Ibrahim

2

Piece of cake, Ibrahim. Before I send this off, which columns would you like to see in the output? Obviously the bib record number. What else?

Bob

3

Thanks Bob. I need the item status, location, Isuppress, and Barcode.

4

Do you use ICODE2 as your item suppression field?

1 Like
5

Yes. we customized it for suppress.

6

Sorry for the delay, Ibrahim; busy day. I added the extra logic for the item status mostly just to make the distinction between “available on shelf” and “not so much available as checked out”:

/*
 * Find bibs with only one attached item.
 * Commented-out condition is included to identify the complement, only bibs with multiple items.
 * See also the commented-out campus_code logic if your library uses INN-Reach.
 * This query assumes ICODE2 is designated as the item suppression field.
 *
 * Bob Gaydos <bgaydos@starklibrary.org>
 * August 23, 2024
 */
SET search_path = 'sierra_view';
WITH SingularItemBibs AS
(
    SELECT
        bil.bib_record_id
    FROM bib_record_item_record_link bil
    INNER JOIN record_metadata rm
       ON rm.id = bil.bib_record_id
    WHERE rm.deletion_date_gmt is NULL             -- ignore any zombie bibs
      AND TRIM(COALESCE(rm.campus_code, '')) = ''  -- ignore virtual items at INN-Reach libraries
    GROUP BY
        bil.bib_record_id
    HAVING
        COUNT(bil.item_record_id) = 1
      --COUNT(bil.item_record_id) > 1  -- to find bibs with multiple attached items instead
)
SELECT
    rm.record_type_code || rm.record_num::VARCHAR(8) || 'a' AS "Bib Record Num",
    CASE
        WHEN i.item_status_code = '-' AND c.due_gmt IS NULL
            THEN i.item_status_code || ' Available'
        WHEN i.item_status_code = '-' AND c.due_gmt IS NOT NULL
            THEN i.item_status_code || ' Checked Out Due ' || due_gmt::TIMESTAMP::DATE::VARCHAR(10)
        ELSE
            i.item_status_code || ' ' || COALESCE(isn.name, '')
    END AS "Item Status",
    i.location_code AS "Location Code",
    i.icode2 AS "Item Suppress",
    string_agg(TRIM(COALESCE(ib.field_content, '')), ', ') AS "Item Barcode"
FROM SingularItemBibs sib
INNER JOIN record_metadata rm
   ON rm.id = sib.bib_record_id
INNER JOIN bib_record_item_record_link bil
   ON bil.bib_record_id = sib.bib_record_id
INNER JOIN item_record i
   ON i.id = bil.item_record_id
LEFT OUTER JOIN item_status_property_myuser isn
   ON isn.code = i.item_status_code
LEFT OUTER JOIN checkout c
   ON c.item_record_id = i.id
LEFT OUTER JOIN varfield ib
   ON ib.record_id = i.id
  AND ib.varfield_type_code = 'b'
GROUP BY
    rm.record_type_code,
    rm.record_num,
    i.item_status_code,
    c.due_gmt,
    isn.name,
    i.location_code,
    i.icode2
ORDER BY
    "Bib Record Num"
;
7

Thank you so much Bob I really appreciate your help.