SQL Query - Bibs with 12 or more circulating items

Sierra, Millennium & Encore
1

Hello all. I was wondering if someone could help me with a SQL query for Sierra. I need a list of bibs with 12 or more circulating items. I’m looking for adult age-level bibs, and need to filter out bibs with locations equaling TMA ,ASCL, and PHF. Thank you in advance for your help.

2

@AnnieW, I can write this for you but first clarify two points:

  1. Please define “circulating items” in terms of permissible status values, suppression codes at the bib and item level, and any other data points which define “circulating” at your library. Please be explicit regarding which ICODE/BCODE columns are used for suppression.
  2. You want to filter out bibs having locations (TMA, ASCL, PHF), but since location_code is a column on the item record, are items in these locations to be ignored when counting a minimum of 12 circulating child items, or is the mere existence of any one of these location codes sufficient to disqualify the bib altogether?
3

Hi @bgaydos - Thank you so much!

  1. Circulating items - any items with check shelf or checked out (-) , on holdshelf (!), in transit (t), or in display (d) statuses.
  2. The mere existence of any of these locations in an item record location code can disqualify the bib altogether.
4 
/*
 * "I need a list of bibs with 12 or more circulating items.
 *  I’m looking for adult age-level bibs, and need to filter out
 *  bibs with locations equaling TMA ,ASCL, and PHF."  -Annie Wicks
 *
 * Sorry Annie, but I forgot to ask you how to identify adult age-level bibs,
 * so this query doesn't filter out J and YA.  Perhaps your call numbers
 * would reliably begin with J or YA for non-adult items, or maybe you would
 * make the distinction by examining the item type.  That'll be a goal for
 * Version 2.
 *
 * Bob Gaydos <bgaydos@starklibrary.org>
 * September 11, 2026
 */
SET search_path = 'sierra_view';
WITH Filtered AS
(
    SELECT
        CASE
            WHEN i.location_code ILIKE 'TMA'
                THEN 0
            WHEN i.location_code ILIKE 'ASCL'
                THEN 0
            WHEN i.location_code ILIKE 'PHF'
                THEN 0
            ELSE
                1
        END AS valid_item,
        bil.bib_record_id
    FROM bib_record_item_record_link bil
    INNER JOIN item_record i
       ON i.id = bil.item_record_id
    WHERE i.item_status_code IN ('-', '!', 'd', 't')
    --AND bib is adult age level, whatever those criteria are
)
SELECT
    rm.record_type_code || rm.record_num::TEXT || 'a' AS "Bib Record Number"
FROM Filtered f
INNER JOIN record_metadata rm
   ON rm.id = f.bib_record_id
GROUP BY
    rm.record_type_code,
    rm.record_num
HAVING SUM(f.valid_item) = COUNT(f.bib_record_id)
   AND COUNT(f.bib_record_id) >= 12
ORDER BY
    "Bib Record Number"
;