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
      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
  *
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