holds_ready_for_pickup_by_month_branch_items

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,
    item.bib_record_num,
    record_num as item_record_num,
    patron_record_hash,
    pickup_location_code
  from
    holds_shelf
    left outer join item as item on item.item_record_num = holds_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,
  :branch_name as branch_name,
  --item_source_location_code,
  (
    select
      --case
      --  when branch_name.name = :branch_name then ' ' || branch_name.name
      --  else coalesce(branch_name.name, item_source_location_code, '')
      --end
      coalesce(branch_name.name, item_source_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
      "location".code = hold_shelf_data.item_source_location_code
    limit
      1
  ) as item_source_branch_name,
  coalesce(
    hold_shelf_data.item_format,
    'Not Available / Deleted'
  ) as item_format,
  hold_shelf_data.date_hold_placed,
  hold_shelf_data.date_hold_on_holdshelf,
  hold_shelf_data.days_to_holdshelf,
  hold_shelf_data.item_record_num,
  hold_shelf_data.bib_record_num,
  (
    select
      best_title
    from
      bib
    where
      bib.bib_record_num = hold_shelf_data.bib_record_num
    limit
      1
  ) as best_title
from
  hold_shelf_data -- left outer join current_collection.bib as bib on bib.bib_record_num = hold_shelf_data.bib_record_num
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
  )
  and hold_shelf_data.item_format = :item_format
order by
  branch_name,
  -- use the trick of placing a ' ' in front of the name to get the branch name to sort to the top
  case
    when item_source_branch_name = :branch_name then ' ' || item_source_branch_name
    else item_source_branch_name
  end,
  days_to_holdshelf

Query parameters

Edit SQL

0 results