This query started its life as a replacement for the Clear Expired Holds and Holdshelf report for one location. I’m posting it here in case anyone can benefit from the report or any of these query features:
- Use of CTEs (Common Table Expressions) to minimize database fetches and conditional comparisons
- Use of a UNION to combine two mutually-exclusive subqueries
- Example of date arithmetic using INTERVAL
- Example of one of the many valuable historical data points captured in circ_trans
- Illustrates how static circ_trans rows can be joined with fluid holds data
/*
* Display the items on a specified holdshelf with relevant details for circ staff and
* diagnostic info for the Sierra admin.
*
* This query originated when a Clear Holdshelf report for one branch was producing no output.
* The addition of the Pickup Location and Origin App columns revealed the source of the problem,
* which was that "readreq" (WebPAC, or BiblioCore in our case) was creating holds using the "lp"
* pickup location, while holds created in the SDA properly use the "loclp" pickup location.
* Both location codes refer to the same physical location, a standalone locker kiosk.
*
* Bob Gaydos <bgaydos@starklibrary.org>
* October 8, 2024
*/
SET search_path = 'sierra_view';
WITH ItemsOnHoldshelf AS
(
-- Minimize database fetches by grabbing all the columns needed for the rest of the query now,
-- since some columns are common to both the OriginApp CTE as well as the main query body.
SELECT
h.id AS hold_id,
h.placed_gmt::DATE AS hold_placed,
h.on_holdshelf_gmt::TIMESTAMP AS on_holdshelf_since,
h.expire_holdshelf_gmt,
h.pickup_location_code AS pickup_location,
h.patron_record_id,
i.id AS item_record_id,
bil.bib_record_id
FROM hold h
INNER JOIN item_record i
ON i.id = h.record_id
INNER JOIN bib_record_item_record_link bil
ON bil.item_record_id = i.id
WHERE i.item_status_code IN ('!', '#') -- # is same as !, but for INN-Reach titles
AND h.pickup_location_code IN ('loclp', 'lp') -- customize for your site
),
OriginApp AS
(
-- Determines whether the hold was placed in the SDA, INN-Reach or the discovery layer.
-- Both subqueries and the UNION are needed here because at the time a hold is placed,
-- it is a bib-level hold or an item-level hold but not both, and although all bib-level
-- holds will become item-leel holds once an item is chosen to fulfill the hold,
-- the circ_trans record is historical and thus does not reflect that transition.
-- Yes, there also exist volume-level holds but we don't have those at our library.
SELECT
ioh.hold_id,
ct.application_name
FROM ItemsOnHoldshelf ioh
INNER JOIN circ_trans ct
ON ct.patron_record_id = ioh.patron_record_id
AND ct.bib_record_id = ioh.bib_record_id -- join on bib ids
WHERE ioh.hold_placed = ct.transaction_gmt::DATE
AND ct.op_code = 'nb' -- bib holds
--
UNION
--
SELECT
ioh.hold_id,
ct.application_name
FROM ItemsOnHoldshelf ioh
INNER JOIN circ_trans ct
ON ct.patron_record_id = ioh.patron_record_id
AND ct.item_record_id = ioh.item_record_id -- join on item ids
WHERE ioh.hold_placed = ct.transaction_gmt::DATE
AND ct.op_code = 'ni' -- item holds (see also the Knights who say this)
)
SELECT
ioh.pickup_location AS "Pickup Location",
COALESCE(o.application_name, '') AS "Origin App",
TRIM(COALESCE(pb.field_content, '')) AS "Patron Barcode",
CASE
WHEN n.id IS NULL
THEN ''
WHEN COALESCE(n.last_name, '') = ''
THEN TRIM(TRIM(TRIM(COALESCE(n.first_name, '')) || ' '
|| TRIM(COALESCE(n.middle_name, ''))) || ' '
|| TRIM(COALESCE(n.suffix, '')))::TEXT
ELSE
TRIM(COALESCE(n.last_name, '')) || ', '
|| TRIM(TRIM(TRIM(COALESCE(n.first_name, '')) || ' '
|| TRIM(COALESCE(n.middle_name, ''))) || ' '
|| TRIM(COALESCE(n.suffix, '')))::TEXT
END AS "Patron Name",
ioh.on_holdshelf_since::VARCHAR(19) AS "On Holdshelf Since",
CASE
WHEN ioh.expire_holdshelf_gmt::TIMESTAMP::DATE = CURRENT_TIMESTAMP::DATE
THEN 'Final Day'
WHEN ioh.expire_holdshelf_gmt < CURRENT_TIMESTAMP - INTERVAL '1 day' -- doesn't really expire until the day after
THEN 'Expired'
ELSE
'' -- left blank to reduce clutter in output
END AS "Status",
ioh.expire_holdshelf_gmt::DATE::VARCHAR(10) AS "Holdshelf Expire",
TRIM(COALESCE(ib.field_content, '')) AS "Item Barcode",
TRIM(LEFT(COALESCE(brp.best_title, ''), 80)) AS "Title",
TRIM(LEFT(COALESCE(brp.best_author, ''), 40)) AS "Author"
FROM ItemsOnHoldshelf ioh
LEFT OUTER JOIN OriginApp o
ON o.hold_id = ioh.hold_id
LEFT OUTER JOIN bib_record_property brp
ON brp.bib_record_id = ioh.bib_record_id
LEFT OUTER JOIN varfield ib
ON ib.record_id = ioh.item_record_id
AND ib.varfield_type_code = 'b'
LEFT OUTER JOIN patron_record_fullname n
ON n.patron_record_id = ioh.patron_record_id
LEFT OUTER JOIN varfield pb
ON pb.record_id = ioh.patron_record_id
AND pb.varfield_type_code = 'b'
AND TRIM(pb.field_content) LIKE '21333%' -- our barcode prefix for physical library cards with borrowing privileges
ORDER BY
"Patron Name",
"Holdshelf Expire",
"Title"
;