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.
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.
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”
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”
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