holds_ready_for_pickup_by_month_branch

Custom SQL query (hide)

with hold_shelf_data as (
  select
    date(modified_epoch, 'unixepoch', 'localtime') as date_hold_on_holdshelf,
    date(placed_epoch, 'unixepoch', 'localtime') as date_hold_placed,
    cast(
      round((modified_epoch - placed_epoch) / 86400.0) as integer
    ) as days_to_holdshelf,
    s_location_code as item_source_location_code,
    item.item_format,
    record_num,
    patron_record_hash,
    pickup_location_code
  from
    holds_shelf as hold_shelf
    left outer join item on item.item_record_num = hold_shelf.record_num
  where
    modified_epoch >= CAST(strftime('%s', :start_date || '-01') AS INT)
    and modified_epoch < CAST(
      strftime('%s', DATE(:start_date || '-01', '+1 months')) AS INT
    )
)
select
  strftime('%Y-%m', :start_date || '-01') as month,
  coalesce(item_format, 'Not Available / Deleted') as item_format,
  round(avg(days_to_holdshelf), 2) as avg_days_to_holdshelf,
  count(record_num) as count_items,
  count(DISTINCT patron_record_hash) as count_distinct_patrons,
  round(
    (
      count(record_num) * 1.0 / count(DISTINCT patron_record_hash) * 1.0
    ),
    2
  ) as avg_items_per_patron,
  'https://ilsweb.cincinnatilibrary.org/collection-analysis/current_collection/holds_ready_for_pickup_by_month_branch_items?branch_name=' || replace(:branch_name, ' ', '%20') || '&start_date=' || :start_date || '&item_format=' || replace(item_format, ' ', '%20') || '&_hide_sql=1' as holds_ready_for_pickup_by_month_branch_items
from
  hold_shelf_data
where
  hold_shelf_data.pickup_location_code in (
    select
      "location".code
    from
      "location"
      left outer join branch on branch.code_num = "location".branch_code_num
      left outer join branch_name on branch_name.branch_id = branch.id
    where
      branch_name.name = :branch_name
  )
group by
  1,
  2
order by
  item_format

Query parameters

Edit SQL

0 results