leased_item_view (view)

11,751 rows

✎ View and edit SQL

This data as json, CSV (advanced)

Suggested facets: checkout_date (date), due_date (date), last_checkout_date (date), last_checkin_date (date), item_creation_date (date), item_record_last_updated (date), bib_cataloging_date (date)

Next page

CREATE VIEW leased_item_view AS with ld_item_info as (
  select
    item.item_format,
    item.bib_record_num,
    item.barcode,
    item.price_cents,
    item.checkout_date,
    item.due_date,
    item.last_checkout_date,
    item.last_checkin_date,
    item.checkout_total,
    -- lucky day items are not renewable
    -- item.renewal_total,
    item.item_status_code,
    item.creation_date as item_creation_date,
    item.record_last_updated
  from
    item
  where
    (
      -- in order to effectively use the index for barcode,
      -- we can't use "LIKE"...
      -- item.barcode like 'L%'
      item.barcode >= 'L000000000000'
      and item.barcode < 'M'
    )
)
select
  ld_item_info.item_format,
  ld_item_info.bib_record_num,
  ld_item_info.barcode,
  ld_item_info.price_cents,
  ld_item_info.checkout_date,
  ld_item_info.due_date,
  ld_item_info.last_checkout_date,
  ld_item_info.last_checkin_date,
  ld_item_info.checkout_total,
  -- ld_item_info.item_status_code,
  p.item_status_name,
  ld_item_info.item_creation_date,
  ld_item_info.record_last_updated as item_record_last_updated,
  bib.best_author,
  bib.best_title,
  bib.publisher,
  bib.publish_year,
  bib.bib_level_callnumber,
  bib.cataloging_date as bib_cataloging_date
from
  ld_item_info
  left join item_status_property as p on p.item_status_code = ld_item_info.item_status_code
  left join bib on bib.bib_record_num = ld_item_info.bib_record_num;