Static SQL Queries & Reports: Holds =================================== The following queries are useful for reporting purposes related to holds. **NOTE** should the `Defining` queries be updated, ensure that the queries re-using those are updated as well. .. contents:: Defining "active holds" ----------------------- `click to run query on "current_collection" `__ **IMPORTANT** remember to update this in any of the queries that utilize this if we make changes to this definition. This query is written so that it can be re-used in conjunction with another query or search where "active holds" are wanting to be considered. .. code-block:: sql with active_holds as ( -- "active holds" -- -------------- -- This will produce a list of holds meeting the following criteria: -- * hold that is not Frozen (except for holds placed by patrons with ptype 196) -- * hold with zero delay days OR the hold delay has passed (hold placed date + delay days is not a date in the future) -- * hold placed by patron with one of the following ptype codes: -- ( 0, 1, 2, 5, 6, 10, 11, 12, 15, 22, 30, 31, 32, 40, 41, 196 ) -- * hold status is "on hold" select h.* from hold as h join record_metadata as r on ( -- TODO figure out if maybe we could just use the `is_ill` boolean value to do this (this is still fast since it's an indexed search) r.record_type_code = 'b' and r.record_num = h.bib_record_num ) -- join the record metadata so that we're only concerning ourselves with titles that belong to us (to filter out ILL holds) where -- * hold that is not Frozen (except for holds placed by patrons with ptype 196) ( h.is_frozen is FALSE OR h.patron_ptype_code = 196 ) AND -- * hold with zero delay days OR the hold delay has passed (hold placed date + delay days is not in the future) ( julianday(datetime('now')) - ( h.placed_julianday + (h.delay_days * 1.0) ) ) > 0 AND -- * hold placed by patron with one of the following ptype codes: -- ( 0, 1, 2, 5, 6, 10, 11, 12, 15, 22, 30, 31, 32, 40, 41, 196 ) h.patron_ptype_code IN ( 0, 1, 2, 5, 6, 10, 11, 12, 15, 22, 30, 31, 32, 40, 41, 196 ) AND -- * hold status is "on hold" h.hold_status = 'on hold' ) select * from active_holds Defining "active items" ----------------------- `click to run query on "current_collection" `__ .. code-block:: sql with active_items as ( -- "active items" -- -------------- -- This will produce a list of items meeting the following criteria: -- * item status is one of the following codes: -- ('-', '!', 'b', 'p', '(', '@', ')', '_', '=', '+', 't') -- * if the item has a due date, then it must be less than 60 days overdue: -- coalesce( (julianday(date('now')) - julianday(item.due_date) > 60.0 ), FALSE) select item.bib_record_num, item.item_record_num, v.volume_record_num, v.volume_statement, v.items_display_order from item left outer join volume_record_item_record_link as v on v.item_record_num = item.item_record_num -- we need to consider volume information for volume-level holds join record_metadata as r on ( r.record_type_code = 'b' and r.record_num = item.bib_record_num and r.campus_code = '' ) -- considers only items belonging to us (no virtual items) where -- * item status is one of the following codes: -- ('-', '!', 'b', 'p', '(', '@', ')', '_', '=', '+', 't') item.item_status_code in ( '-', '!', 'b', 'p', '(', '@', ')', '_', '=', '+', 't' ) -- * if the item has a due date, then it must be less than 60 days overdue: -- coalesce( (julianday(date('now')) - julianday(item.due_date) > 60.0 ), FALSE) and coalesce( ( julianday(date('now')) - julianday(item.due_date) > 60.0 ), FALSE ) is FALSE ) select * from active_items ---- "active holds" by title (sorted by count "active holds") -------------------------------------------------------- `click to run query on "current_collection" `__ .. code-block:: sql with active_holds as ( -- "active holds" -- -------------- -- This will produce a list of holds meeting the following criteria: -- * hold that is not Frozen (except for holds placed by patrons with ptype 196) -- * hold with zero delay days OR the hold delay has passed (hold placed date + delay days is not a date in the future) -- * hold placed by patron with one of the following ptype codes: -- ( 0, 1, 2, 5, 6, 10, 11, 12, 15, 22, 30, 31, 32, 40, 41, 196 ) -- * hold status is "on hold" select h.* from hold as h join record_metadata as r on ( -- TODO figure out if maybe we could just use the `is_ill` boolean value to do this (this is still fast since it's an indexed search) r.record_type_code = 'b' and r.record_num = h.bib_record_num and r.campus_code = '' ) -- join the record metadata so that we're only concerning ourselves with titles that belong to us (to filter out ILL holds) where -- * hold that is not Frozen (except for holds placed by patrons with ptype 196) ( h.is_frozen is FALSE OR h.patron_ptype_code = 196 ) AND -- * hold with zero delay days OR the hold delay has passed (hold placed date + delay days is not in the future) ( julianday(datetime('now')) - ( julianday(h.placed_gmt) + (h.delay_days * 1.0) ) ) > 0 AND -- * hold placed by patron with one of the following ptype codes: -- ( 0, 1, 2, 5, 6, 10, 11, 12, 15, 22, 30, 31, 32, 40, 41, 196 ) h.patron_ptype_code IN ( 0, 1, 2, 5, 6, 10, 11, 12, 15, 22, 30, 31, 32, 40, 41, 196 ) AND -- * hold status is "on hold" h.hold_status = 'on hold' ) select a.bib_record_num, count(a.bib_record_num) as count_active_holds, round( avg( julianday('now') - (julianday(a.placed_gmt) + (a.delay_days * 1.0)) ), 2 ) as average_age_days_of_hold, bib.best_author, bib.best_title, bib.publish_year, bib.creation_date as bib_creation_date, bib.bib_level_callnumber from active_holds as a join bib on bib.bib_record_num = a.bib_record_num group by a.bib_record_num order by count_active_holds desc