Holds - plch/sierra-sql GitHub Wiki
Generate hold_removed data
select
extract(epoch from hr.removed_gmt)::INTEGER as removed_ts_utc,
extract(epoch from hr.placed_gmt)::INTEGER as placed_ts_utc,
extract(epoch from hr.on_holdshelf_gmt)::INTEGER as on_holdshelf_ts_utc,
extract(epoch from hr.expires_gmt)::INTEGER as expires_ts_utc,
extract(epoch from hr.expire_holdshelf_gmt)::INTEGER as expires_holdshelf_ts_utc,
case hr.status
when '0' then 'On hold'
when 'b' then 'Bib hold ready for pickup'
when 'j' then 'Volume hold ready for pickup'
when 'i' then 'Item hold ready for pickup'
when 't' then 'Bib, item, or volume in transit to pickup location'
else hr.status
end as hold_status,
case hr.holdshelf_status
when '!' then 'ON HOLDSHELF'
when 'm' then 'MISSING'
when '-' then 'AVAILABLE'
when 'p' then 'PICKED UP'
when 'c' then 'CANCELLED'
else hr.holdshelf_status
end as holdshelf_status,
rmb.record_num as bib_record_num,
rmb.campus_code as bib_record_campus_code,
rmp.record_num as patron_record_num,
rmp.campus_code as patron_campus_code,
rmb.record_num as bib_record_num,
rmb.campus_code as bib_campus_code,
rmi.record_num as item_record_num,
rmv.record_num as vol_record_num,
hr.is_frozen,
hr.delay_days,
hr.location_code,
hr.is_ir,
hr.pickup_location_code,
hr.note,
hr.ir_pickup_location_code,
hr.ir_print_name,
hr.ir_delivery_stop_name,
hr.is_ir_converted_request,
hr.patron_records_display_order,
hr.records_display_order,
hr.removed_by_user,
hr.removed_by_process,
hr.removed_by_program
from
sierra_view.hold_removed as hr
left outer join sierra_view.bib_record_item_record_link as brirl on
brirl.item_record_id = hr.record_id
left outer join sierra_view.bib_record_volume_record_link as brvrl on
brvrl.volume_record_id = hr.record_id
-- we can use a join here, because we require a bib_record in our output
join sierra_view.record_metadata as rmb on
-- bib_record is found by either one of the top two joins returning
-- non-NULL values, otherwise the hr.record_id is the bib record number
rmb.id = coalesce(
brirl.bib_record_id, -- the record in hold_removed is an item
brvrl.bib_record_id, -- the record in hold_removed is a volume
hr.record_id -- the record in hold_removed is a bib
)
join sierra_view.record_metadata as rmp on
rmp.id = hr.patron_record_id
left outer join sierra_view.record_metadata as rmi on
rmi.id = brirl.item_record_id
left outer join sierra_view.record_metadata as rmv on
rmv.id = brvrl.volume_record_id
Generate JSON data for hold data
with hold_data as (
select
h.id as hold_id,
extract(epoch from h.placed_gmt)::INTEGER as placed_ts_utc,
extract(epoch from h.on_holdshelf_gmt)::INTEGER as on_holdshelf_ts_utc,
extract(epoch from h.expires_gmt)::INTEGER as expires_ts_utc,
case h.status
when '0' then 'On hold'
when 'b' then 'Bib hold ready for pickup'
when 'j' then 'Volume hold ready for pickup'
when 'i' then 'Item hold ready for pickup'
when 't' then 'Bib, item, or volume in transit to pickup location'
else h.status
end as hold_status,
rmp.record_num as patron_record_num,
nullif(rmp.campus_code, '') as patron_campus_code,
rmb.record_num as bib_record_num,
nullif(rmb.campus_code, '') as bib_campus_code,
rmi.record_num as item_record_num,
rmv.record_num as vol_record_num,
json_build_object(
'record_on_hold', (rm.record_type_code || rm.record_num::text || 'a'),
'author_title', (
select
coalesce(nullif(brp.best_author, '') || '/', '') ||
CASE
WHEN length(brp.best_title) > 20 then trim(substring(brp.best_title FROM 1 FOR 17)) || '...'
ELSE brp.best_title
END
from
sierra_view.bib_record_property as brp
where
brp.bib_record_id = rmb.id
limit 1
),
'vol_stmt', (
select
v.field_content
from
sierra_view.varfield as v
where
v.record_id = brvrl.volume_record_id
and v.varfield_type_code = 'v'
order by
v.occ_num
limit 1
),
'bib_campus_code', rmb.campus_code,
'item_barcode', (
select
irp.barcode
from
sierra_view.item_record_property as irp
where
irp.item_record_id = brirl.item_record_id
),
'patron_barcode', (
select
v.field_content
from
sierra_view.varfield as v
where
v.record_id = h.patron_record_id
and v.varfield_type_code = 'b'
order by
v.occ_num
limit 1
), -- as patron_barcode1,
'patron_campus_code', rmp.campus_code,
'is_frozen', is_frozen,
'delay_days', delay_days,
'location_code', location_code,
'expires_gmt', expires_gmt,
-- status
'hold_status', case h.status
when '0' then 'On hold'
when 'b' then 'Bib hold ready for pickup'
when 'j' then 'Volume hold ready for pickup'
when 'i' then 'Item hold ready for pickup'
when 't' then 'Bib, item, or volume in transit to pickup location'
else h.status
end,
'is_ir', is_ir,
'pickup_location_code', pickup_location_code,
'is_ill', is_ill,
'note', note,
'ir_pickup_location_code', ir_pickup_location_code,
'ir_print_name', ir_print_name,
'ir_delivery_stop_name', ir_delivery_stop_name,
'is_ir_converted_request', is_ir_converted_request,
'patron_records_display_order', 'patron_records_display_order',
'records_display_order', records_display_order,
'on_holdshelf_gmt', on_holdshelf_gmt,
'expire_holdshelf_gmt', expire_holdshelf_gmt
) as hold_json_data
from
sierra_view."hold" as h
join sierra_view.record_metadata as rm on
rm.id = h.record_id
left outer join sierra_view.bib_record_item_record_link as brirl on (
-- assume this is an item
brirl.item_record_id = h.record_id
)
left outer join sierra_view.bib_record_volume_record_link as brvrl on (
-- assume this is a volume record
brvrl.volume_record_id = h.record_id
)
left outer join sierra_view.varfield v on (
v.record_id = brvrl.volume_record_id
and v.varfield_type_code = 'v'
)
left outer join sierra_view.record_metadata as rmb on (
rmb.id = coalesce (
brirl.bib_record_id, -- is the record on hold an item?
brvrl.bib_record_id, -- is the record on hold a volume?
h.record_id -- if neither, then it must be a bib on hold.
)
)
left outer join sierra_view.record_metadata as rmp on (
rmp.id = h.patron_record_id
)
left outer join sierra_view.record_metadata as rmi on (
rmi.id = brirl.item_record_id
)
left outer join sierra_view.record_metadata as rmv on (
rmv.id = brvrl.volume_record_id
)
)
select
hold_id,
placed_ts_utc,
on_holdshelf_ts_utc,
expires_ts_utc,
hold_status,
patron_record_num,
patron_campus_code,
bib_record_num,
bib_campus_code,
item_record_num,
vol_record_num,
hold_json_data
from
hold_data as hd
Resolve a bib number from a hold
Find the bib id regardless of what type of hold it is. (e.g. item-level, volume-level, bib-level)
with id_data as (
select
h.id as hold_id,
h.patron_record_id,
(
select
rm2.record_num
from
sierra_view.record_metadata as rm2
where
rm2.id = h.patron_record_id
limit 1
) as patron_record_num,
h.record_id,
rm.record_type_code,
rm.record_num,
coalesce(
brirl.bib_record_id, -- bib_record_id from the item record link (if there is one)
brvrl.volume_record_id, -- OR bib_record_id from the volume record link (")
h.record_id -- OR bib_record_id from the hold (which, by process of elimination can only be the bib)
) as bib_record_id
from
sierra_view."hold" as h
left outer join sierra_view.record_metadata rm on
rm.id = h.record_id
left outer join sierra_view.bib_record_item_record_link brirl on (
-- assume this is an item
brirl.item_record_id = h.record_id
)
left outer join sierra_view.bib_record_volume_record_link brvrl on (
-- assume this is a volume record
brvrl.volume_record_id = h.record_id
)
)
select
*,
(
select
rm.record_num
from
sierra_view.record_metadata as rm
where
rm.id = id_data.bib_record_id
) as bib_record_num,
brp.best_title, -- now we can be sure that we have the bib to grab
brp.best_author, -- additional bib data as needed.
brp.publish_year
from
id_data
join sierra_view.bib_record_property as brp on
brp.bib_record_id = id_data.bib_record_id
Freaky-holds
“Freaky” Holds Active holds criteria:
- hold IS either bib 'b' OR volume= 'j' level
- hold.is_ir IS false (not INN-Reach)
- hold.is_ill IS false (not ILL)
- hold.is_frozen IS false -- not frozen hold
- patrons placing the hold has a ptype code IN (0, 1, 2, 3, 5, 6, 10, 11, 12, 15, 22, 30, 31, 32, 40, 41, 196)
- hold age (adjusted for delay_days) IS greater than 30
Title or Volume has attached items that have status - OR (g OR m) AND are NOT checked out.
DROP TABLE IF EXISTS temp_freaky_holds;
CREATE TEMP TABLE temp_freaky_holds AS
SELECT
CASE
-- we are not going to look at item level holds as part of this report, but could be useful later on...
-- WHEN r.record_type_code = 'i' THEN (
-- SELECT
-- l.bib_record_id
-- FROM
-- sierra_view.bib_record_item_record_link as l
-- WHERE
-- l.item_record_id = h.record_id
-- LIMIT 1
-- )
WHEN r.record_type_code = 'j' THEN (
SELECT
l.bib_record_id
FROM
sierra_view.bib_record_volume_record_link as l
WHERE
l.volume_record_id = h.record_id
LIMIT 1
)
WHEN r.record_type_code = 'b' THEN (
h.record_id
)
ELSE NULL
END AS bib_record_id,
h.record_id,
r.record_type_code AS record_type_code,
h.id as hold_id,
h.patron_record_id,
p.ptype_code,
h.placed_gmt,
h.delay_days,
-- age of hold in days
(( extract(epoch FROM age(
(h.placed_gmt + concat(h.delay_days, ' days')::INTERVAL)
)) / 3600 ) / 24 )::int AS age_days
FROM
sierra_view.hold as h
JOIN
sierra_view.record_metadata as r
ON
r.id = h.record_id
JOIN
sierra_view.patron_record as p
ON
p.record_id = h.patron_record_id
WHERE
(r.record_type_code = 'b' OR r.record_type_code = 'j')
AND h.is_ir is false -- not INN-Reach
AND h.is_ill is false -- not ILL
AND h.is_frozen is false -- not frozen hold
AND p.ptype_code IN (0, 1, 2, 3, 5, 6, 10, 11, 12, 15, 22, 30, 31, 32, 40, 41, 196)
AND (( extract(epoch FROM age(
(h.placed_gmt + concat(h.delay_days, ' days')::INTERVAL)
)) / 3600 ) / 24 )::INTEGER > 30
;
-- TESTING
-- SELECT * FROM temp_freaky_holds LIMIT 100;
-- aggregate the holds into the title, and count them
DROP TABLE IF EXISTS temp_freaky_holds_titles;
CREATE TEMP TABLE temp_freaky_holds_titles AS
SELECT
t.bib_record_id,
t.record_id,
t.record_type_code,
count(t.bib_record_id) AS count_holds
FROM
temp_freaky_holds AS t
GROUP BY
t.bib_record_id,
t.record_id,
t.record_type_code
;
-- testing
-- SELECT * FROM temp_freaky_holds_titles LIMIT 100;
-- get attached items
DROP TABLE IF EXISTS temp_freaky_holds_item_status;
CREATE TEMP TABLE temp_freaky_holds_item_status AS
SELECT
*,
CASE
WHEN t.record_type_code = 'b' THEN
(
SELECT
string_agg(DISTINCT i.location_code, ', ')
FROM
sierra_view.bib_record_item_record_link AS l
JOIN
sierra_view.item_record as i
ON
i.record_id = l.item_record_id
-- don't include the item in the list if it's checked out
LEFT OUTER JOIN
sierra_view.checkout as c
ON
c.item_record_id = l.item_record_id
WHERE
l.bib_record_id = t.record_id
AND c.id IS NULL
AND i.item_status_code = '-'
)
WHEN t.record_type_code = 'j' THEN (
SELECT
string_agg(DISTINCT i.location_code, ', ')
FROM
sierra_view.volume_record_item_record_link AS l
JOIN
sierra_view.bib_record_item_record_link as il
ON
il.item_record_id = l.item_record_id
JOIN
sierra_view.item_record as i
ON
i.record_id = il.item_record_id
-- don't include the item in the list if it's checked out
LEFT OUTER JOIN
sierra_view.checkout as c
ON
c.item_record_id = l.item_record_id
WHERE
l.volume_record_id = t.record_id
AND c.id IS NULL
AND i.item_status_code = '-'
)
ELSE NULL
END AS items_status_dash,
CASE
WHEN t.record_type_code = 'b' THEN
(
SELECT
string_agg(DISTINCT i.location_code, ', ')
FROM
sierra_view.bib_record_item_record_link AS l
JOIN
sierra_view.item_record as i
ON
i.record_id = l.item_record_id
-- don't include the item in the list if it's checked out
LEFT OUTER JOIN
sierra_view.checkout as c
ON
c.item_record_id = l.item_record_id
WHERE
l.bib_record_id = t.record_id
AND c.id IS NULL
AND (i.item_status_code = 'g' OR i.item_status_code = 'm')
)
WHEN t.record_type_code = 'j' THEN (
SELECT
string_agg(DISTINCT i.location_code, ', ')
FROM
sierra_view.volume_record_item_record_link AS l
JOIN
sierra_view.bib_record_item_record_link as il
ON
il.item_record_id = l.item_record_id
JOIN
sierra_view.item_record as i
ON
i.record_id = il.item_record_id
-- don't include the item in the list if it's checked out
LEFT OUTER JOIN
sierra_view.checkout as c
ON
c.item_record_id = l.item_record_id
WHERE
l.volume_record_id = t.record_id
AND c.id IS NULL
AND (i.item_status_code = 'g' OR i.item_status_code = 'm')
)
ELSE NULL
END AS items_status_g_or_m
FROM
temp_freaky_holds_titles as t
;
-- DROP TABLE temp_freaky_holds;
-- DROP TABLE temp_freaky_holds_titles;
-- output
SELECT
r.record_type_code || r.record_num || 'a' as bib_record_num,
t.count_holds,
(
-- SELECT
-- v.field_content as callnumber
SELECT
-- get the call number strip the subfield indicators
regexp_replace(trim(v.field_content), '(\|[a-z]{1})', '', 'ig')
FROM
sierra_view.varfield as v
WHERE
v.record_id = t.bib_record_id
AND v.varfield_type_code = 'c'
ORDER BY
v.occ_num
LIMIT 1
) as callnumber,
CASE
WHEN t.record_type_code = 'j' THEN (
-- t.record_id
SELECT
v.field_content
FROM
sierra_view.varfield as v
WHERE
v.record_id = t.record_id
AND v.varfield_type_code = 'v'
ORDER BY
v.occ_num
LIMIT 1
)
ELSE NULL
END AS volume,
b.best_title,
b.best_author,
t.items_status_dash,
t.items_status_g_or_m
FROM
temp_freaky_holds_item_status as t
JOIN
sierra_view.record_metadata as r
ON
r.id = t.bib_record_id
JOIN
sierra_view.bib_record_property as b
ON
b.bib_record_id = t.bib_record_id
WHERE
t.items_status_dash IS NOT NULL
OR t.items_status_g_or_m IS NOT NULL
;
90 day holds reports
Bib-level 90 day holds report (testing)
SELECT
hold.record_id,
(
SELECT
-- h.record_id,
string_agg(h_list.pickup_location_code, ',') as pickup_location_list
FROM
sierra_view.hold as h_list
WHERE
h_list.record_id = hold.record_id
GROUP BY
h_list.record_id
) as pickup_location_list,
(
-- get the count of OS over 90 days on hold
SELECT
COUNT(*)
FROM
sierra_view.hold as h_count
WHERE
h_count.record_id = hold.record_id
AND h_count.pickup_location_code = 'os'
AND h_count.is_frozen = 'f'
AND (EXTRACT(epoch FROM (SELECT (NOW() - h_count.placed_gmt)))/86400)::int > 90
) as count_os_over_90,
bib_view.record_num,
bib_view.title,
date(bib_view.cataloging_date_gmt) as cat_date,
bib_view.bcode2,
COUNT (bib_view.id) AS nbr_holds,
(
SELECT
sierra_view.varfield_view.field_content
FROM
sierra_view.varfield_view
WHERE
sierra_view.varfield_view.record_id = sierra_view.bib_view.id
AND sierra_view.varfield_view.record_type_code = 'b'
AND sierra_view.varfield_view.varfield_type_code = 'c'
LIMIT 1
) as callnum,
(
SELECT
sierra_view.varfield_view.field_content
FROM
sierra_view.varfield_view
WHERE
sierra_view.varfield_view.record_id = sierra_view.bib_view.id
AND sierra_view.varfield_view.record_type_code = 'b'
AND sierra_view.varfield_view.varfield_type_code = 'a'
LIMIT 1
) as author,
(
SELECT
sierra_view.varfield_view.field_content
FROM
sierra_view.varfield_view
WHERE
sierra_view.varfield_view.record_id = sierra_view.bib_view.id
AND sierra_view.varfield_view.record_type_code = 'b'
AND sierra_view.varfield_view.varfield_type_code = 'p'
LIMIT 1
) as pubinfo,
(
SELECT
COUNT (item_record.id) as bib_items
FROM
sierra_view.bib_record_item_record_link , sierra_view.item_record
WHERE
sierra_view.bib_record_item_record_link.bib_record_id = hold.record_id
AND sierra_view.bib_record_item_record_link.item_record_id = item_record.id
) as nbr_items,
(
SELECT
COUNT(item_record.id ) as bib_items_active
FROM
sierra_view.bib_record_item_record_link,
sierra_view.item_record
LEFT OUTER JOIN sierra_view.checkout
ON
sierra_view.item_record.id = sierra_view.checkout.item_record_id
JOIN sierra_view.record_metadata
ON
sierra_view.record_metadata.id = sierra_view.item_record.id
WHERE
sierra_view.bib_record_item_record_link.bib_record_id = hold.record_id
AND sierra_view.bib_record_item_record_link.item_record_id = item_record.id
-- AND item_record.item_status_code IN ( '-', 't', '!', 'b', 'p', '(', '\@', ')', '_', '=', '+' )
AND (item_record.item_status_code IN ( '-', '!', 'b', 'p', '(', '\@', ')', '_', '=', '+' ) -- #20161116 only items in-transit for fewer than 60 days are active, per VM
OR (item_record.item_status_code = 't' AND record_metadata.record_type_code = 'i'
AND record_metadata.record_last_updated_gmt > now() - interval '60 days' ))
AND ( -- #consider longoverdue items to be not active
sierra_view.checkout.due_gmt is null -- #item isnt checked out
OR sierra_view.checkout.due_gmt > current_date - interval '60 days' -- #item is checked out, but due date after 60days ago
)
) as nbr_active_items,
(
SELECT
COUNT (item_record.id ) as bib_items_inporcessing
FROM
sierra_view.bib_record_item_record_link , sierra_view.item_record
WHERE
sierra_view.bib_record_item_record_link.bib_record_id = hold.record_id
AND sierra_view.bib_record_item_record_link.item_record_id = item_record.id
AND item_record.item_status_code IN ( 'p' )
) as nbr_inprocessing_items,
(
SELECT
SUM(order_record_cmf.copies) as order_copies
FROM
sierra_view.bib_record_order_record_link,
sierra_view.order_view,
sierra_view.order_record_cmf
WHERE
hold.record_id = bib_record_order_record_link.bib_record_id
AND bib_record_order_record_link.order_record_id = order_view.id
AND order_view.record_id = order_record_cmf.order_record_id
AND order_view.received_date_gmt is null
AND order_record_cmf.location_code != 'multi'
GROUP BY hold.record_id
) as nbr_ordered_copies
FROM
sierra_view.hold
JOIN sierra_view.bib_view
ON
hold.record_id = bib_view.id
AND bib_view.cataloging_date_gmt IS NOT NULL
JOIN sierra_view.patron_record
ON
hold.patron_record_id = patron_record.id
AND patron_record.ptype_code IN ( 0 , 1 , 2 , 5 , 6 , 10 , 11 , 12 , 15 , 22 , 30 , 31 , 32 , 40 , 41, 196 )
WHERE (
(
hold.is_frozen = 'f'
AND (
(hold.delay_days = 0)
OR (EXTRACT(epoch FROM (SELECT (NOW() - hold.placed_gmt)))/86400::int > delay_days)
)
)
OR ( patron_record.ptype_code = 196 )
) -- # Changed criteria to allow frozen/delay_days holds for Admin cards 20160504 LMK
-- AND (hold.placed_gmt < current_date - interval '90 days' ) removed RV
AND (EXTRACT(epoch FROM (SELECT (NOW() - hold.placed_gmt)))/86400) > 90.0
GROUP BY
hold.record_id,
-- hold.pickup_location_code,
bib_view.id,
bib_view.record_num,
bib_view.title,
bib_view.cataloging_date_gmt,
bib_view.bcode2
ORDER BY
title,
bcode2,
callnum
Bib-level 90 day holds report (currently in production as of 2017-07-14)
SELECT
hold.record_id,
bib_view.record_num,
bib_view.title,
date(bib_view.cataloging_date_gmt) as cat_date,
bib_view.bcode2,
COUNT (bib_view.id) AS nbr_holds,
(
SELECT
sierra_view.varfield_view.field_content
FROM
sierra_view.varfield_view
WHERE
sierra_view.varfield_view.record_id = sierra_view.bib_view.id
AND sierra_view.varfield_view.record_type_code = 'b'
AND sierra_view.varfield_view.varfield_type_code = 'c'
LIMIT 1
) as callnum,
(
SELECT
sierra_view.varfield_view.field_content
FROM
sierra_view.varfield_view
WHERE
sierra_view.varfield_view.record_id = sierra_view.bib_view.id
AND sierra_view.varfield_view.record_type_code = 'b'
AND sierra_view.varfield_view.varfield_type_code = 'a'
LIMIT 1
) as author,
(
SELECT
sierra_view.varfield_view.field_content
FROM
sierra_view.varfield_view
WHERE
sierra_view.varfield_view.record_id = sierra_view.bib_view.id
AND sierra_view.varfield_view.record_type_code = 'b'
AND sierra_view.varfield_view.varfield_type_code = 'p'
LIMIT 1
) as pubinfo,
(
SELECT
COUNT (item_record.id) as bib_items
FROM
sierra_view.bib_record_item_record_link , sierra_view.item_record
WHERE
sierra_view.bib_record_item_record_link.bib_record_id = hold.record_id
AND sierra_view.bib_record_item_record_link.item_record_id = item_record.id
) as nbr_items,
(
SELECT
COUNT(item_record.id ) as bib_items_active
FROM
sierra_view.bib_record_item_record_link,
sierra_view.item_record
LEFT OUTER JOIN sierra_view.checkout
ON
sierra_view.item_record.id = sierra_view.checkout.item_record_id
JOIN sierra_view.record_metadata
ON
sierra_view.record_metadata.id = sierra_view.item_record.id
WHERE
sierra_view.bib_record_item_record_link.bib_record_id = hold.record_id
AND sierra_view.bib_record_item_record_link.item_record_id = item_record.id
-- AND item_record.item_status_code IN ( '-', 't', '!', 'b', 'p', '(', '\@', ')', '_', '=', '+' )
AND (item_record.item_status_code IN ( '-', '!', 'b', 'p', '(', '\@', ')', '_', '=', '+' ) -- #20161116 only items in-transit for fewer than 60 days are active, per VM
OR (item_record.item_status_code = 't' AND record_metadata.record_type_code = 'i'
AND record_metadata.record_last_updated_gmt > now() - interval '60 days' ))
AND ( -- #consider longoverdue items to be not active
sierra_view.checkout.due_gmt is null -- #item isnt checked out
OR sierra_view.checkout.due_gmt > current_date - interval '60 days' -- #item is checked out, but due date after 60days ago
)
) as nbr_active_items,
(
SELECT
COUNT (item_record.id ) as bib_items_inporcessing
FROM
sierra_view.bib_record_item_record_link , sierra_view.item_record
WHERE
sierra_view.bib_record_item_record_link.bib_record_id = hold.record_id
AND sierra_view.bib_record_item_record_link.item_record_id = item_record.id
AND item_record.item_status_code IN ( 'p' )
) as nbr_inprocessing_items,
(
SELECT
SUM(order_record_cmf.copies) as order_copies
FROM
sierra_view.bib_record_order_record_link,
sierra_view.order_view,
sierra_view.order_record_cmf
WHERE
hold.record_id = bib_record_order_record_link.bib_record_id
AND bib_record_order_record_link.order_record_id = order_view.id
AND order_view.record_id = order_record_cmf.order_record_id
AND order_view.received_date_gmt is null
AND order_record_cmf.location_code != 'multi'
GROUP BY hold.record_id
) as nbr_ordered_copies
FROM
sierra_view.hold
JOIN sierra_view.bib_view
ON
hold.record_id = bib_view.id
AND bib_view.cataloging_date_gmt IS NOT NULL
JOIN sierra_view.patron_record
ON
hold.patron_record_id = patron_record.id
AND patron_record.ptype_code IN ( 0 , 1 , 2 , 5 , 6 , 10 , 11 , 12 , 15 , 22 , 30 , 31 , 32 , 40 , 41, 196 )
WHERE (
(
hold.is_frozen = 'f'
AND (
(hold.delay_days = 0)
OR (EXTRACT(epoch FROM (SELECT (NOW() - placed_gmt)))/86400::int > delay_days)
)
)
OR ( patron_record.ptype_code = 196 )
) -- # Changed criteria to allow frozen/delay_days holds for Admin cards 20160504 LMK
AND (hold.placed_gmt < current_date - interval '90 days' )
GROUP BY
hold.record_id,
bib_view.id,
bib_view.record_num,
bib_view.title,
bib_view.cataloging_date_gmt,
bib_view.bcode2
ORDER BY
bcode2,
callnum,
title
Volume-level 90 day holds report (currently in production as of 2017-07-14)
SELECT
hold.record_id,
bib_view.id,
bib_view.record_num,
bib_view.title,
date(bib_view.cataloging_date_gmt) as cat_date,
bib_view.bcode2,
COUNT (bib_view.id) as nbr_holds,
(
SELECT
sierra_view.varfield_view.field_content
FROM
sierra_view.varfield_view
WHERE
sierra_view.varfield_view.record_id = sierra_view.bib_view.id
AND sierra_view.varfield_view.record_type_code = 'b'
AND sierra_view.varfield_view.varfield_type_code = 'c'
LIMIT
1
) as callnum,
(
SELECT
sierra_view.varfield_view.field_content
FROM
sierra_view.varfield_view
WHERE
sierra_view.varfield_view.record_id = sierra_view.bib_view.id
AND sierra_view.varfield_view.record_type_code = 'b'
AND sierra_view.varfield_view.varfield_type_code = 'a'
LIMIT
1
) as author,
(
SELECT
sierra_view.varfield_view.field_content
FROM
sierra_view.varfield_view
WHERE
sierra_view.varfield_view.record_id = sierra_view.bib_view.id
AND sierra_view.varfield_view.record_type_code = 'b'
AND sierra_view.varfield_view.varfield_type_code = 'p'
LIMIT
1
) as pubinfo,
(
SELECT
COUNT (item_record.id) as vol_items
FROM
sierra_view.volume_record_item_record_link,
sierra_view.item_record
WHERE
sierra_view.volume_record_item_record_link.volume_record_id = hold.record_id
AND sierra_view.volume_record_item_record_link.item_record_id = item_record.id
) as nbr_items,
(
SELECT
COUNT (item_record.id ) AS vol_items_active
FROM
sierra_view.volume_record_item_record_link,
sierra_view.item_record
LEFT OUTER JOIN
sierra_view.checkout
ON
sierra_view.item_record.id = sierra_view.checkout.item_record_id
JOIN sierra_view.record_metadata
ON
sierra_view.record_metadata.id = sierra_view.item_record.id
WHERE
sierra_view.volume_record_item_record_link.volume_record_id = hold.record_id
AND sierra_view.volume_record_item_record_link.item_record_id = item_record.id
-- AND item_record.item_status_code IN ( '-', 't', '!', 'b', 'p', '(', '\@', ')', '_', '=', '+' )
AND (
item_record.item_status_code IN ( '-', '!', 'b', 'p', '(', '\@', ')', '_', '=', '+' ) -- #20161116 only items in-transit for fewer than 60 days are active, per VM
OR (
item_record.item_status_code = 't'
AND record_metadata.record_type_code = 'i'
AND record_metadata.record_last_updated_gmt > now() - interval '60 days'
)
)
-- consider longoverdue items to be not active
AND (
sierra_view.checkout.due_gmt is null --item isnt checked out
OR sierra_view.checkout.due_gmt > current_date - interval '60 days' -- #item is checked out, but due date after 60days ago
)
) as nbr_active_items,
(
SELECT
COUNT
(item_record.id ) AS bib_items_inporcessing
FROM
sierra_view.bib_record_item_record_link,
sierra_view.item_record
WHERE
sierra_view.bib_record_item_record_link.bib_record_id = hold.record_id
AND sierra_view.bib_record_item_record_link.item_record_id = item_record.id
AND item_record.item_status_code IN ( 'p' )
) as nbr_inprocessing_items,
(
SELECT
SUM(order_record_cmf.copies) AS order_copies
FROM
sierra_view.bib_record_order_record_link,
sierra_view.order_view,
sierra_view.order_record_cmf
WHERE
hold.record_id = bib_record_order_record_link.bib_record_id
AND bib_record_order_record_link.order_record_id = order_view.id
AND order_view.record_id = order_record_cmf.order_record_id
AND order_view.received_date_gmt is null
AND order_record_cmf.location_code != 'multi'
GROUP BY
hold.record_id
) as nbr_ordered_copies,
(
SELECT
sierra_view.varfield_view.field_content
FROM
sierra_view.varfield_view
WHERE
sierra_view.varfield_view.record_id = sierra_view.volume_record.id
AND sierra_view.varfield_view.record_type_code = 'j'
AND sierra_view.varfield_view.varfield_type_code = 'v'
ORDER BY
field_content DESC
LIMIT 1
) as volume_statement
FROM sierra_view.hold
JOIN sierra_view.volume_record
ON
hold.record_id = volume_record.id
JOIN
sierra_view.bib_record_volume_record_link
ON
volume_record.record_id = bib_record_volume_record_link.volume_record_id
JOIN sierra_view.bib_view
ON
bib_record_volume_record_link.bib_record_id = bib_view.id
AND bib_view.bcode2 NOT IN ( 's', 'n' )
AND bib_view.cataloging_date_gmt IS NOT NULL
JOIN sierra_view.patron_record
ON
hold.patron_record_id = patron_record.id
AND patron_record.ptype_code IN ( 0 , 1 , 2 , 5 , 6 , 10 , 11 , 12 , 15 , 22 , 30 , 31 , 32 , 40 , 41, 196 )
WHERE
(
(
hold.is_frozen = 'f'
AND (
(hold.delay_days = 0)
OR (EXTRACT(epoch FROM (SELECT (NOW() - placed_gmt)))/86400::int > delay_days)
)
)
OR ( patron_record.ptype_code = 196 )
) -- # Changed criteria to allow frozen/delay_days holds for Admin cards 20160504 LMK
AND (hold.placed_gmt < current_date - interval '90 days' )
GROUP BY
hold.record_id,
bib_view.id,
bib_view.record_num,
bib_view.title,
bib_view.cataloging_date_gmt,
bib_view.bcode2,
sierra_view.volume_record.id
ORDER BY
bcode2,
callnum,
title
Find holds that are greater than a certain number of days (365 in the example below)
SELECT
-- number of days the hold has been active (86400 seconds per day)
(EXTRACT(EPOCH FROM current_timestamp - placed_gmt)/86400)::int as days_active,
r.record_type_code || r.record_num || 'a' as record_num,
pr.record_type_code || pr.record_num || 'a' as patron_record_num,
h.placed_gmt,
h.expires_gmt,
h.is_frozen,
h.delay_days,
h.is_ill
FROM
sierra_view.hold AS h
JOIN
sierra_view.record_metadata as r
ON
r.id = h.record_id
JOIN
sierra_view.record_metadata as pr
ON
pr.id = h.patron_record_id
WHERE
(EXTRACT(EPOCH FROM current_timestamp - placed_gmt)/86400)::int > 365
AND h.is_frozen = false
ORDER BY
days_active DESC
Get bib information from holds that are INN-Reach or ILL
-----
-- this query will get hold, bib, and item information from holds that are
-- INN-Reach or ILL
-----
DROP TABLE IF EXISTS temp_holds_data;
CREATE TEMP TABLE temp_holds_data AS
SELECT
p.ptype_code,
p.home_library_code as patron_home_library_code,
n.last_name || ', ' ||n.first_name || COALESCE(' ' || NULLIF(n.middle_name, ''), '') AS "patron_name",
r.record_type_code || r.record_num as record_num,
CASE
WHEN r.record_type_code = 'i' THEN (
SELECT
-- i.item_status_code
CASE
WHEN i.item_status_code = '-' THEN 'AVAILABLE'
WHEN i.item_status_code = 'm' THEN 'MISSING'
WHEN i.item_status_code = 'z' THEN 'CL RETURNED'
WHEN i.item_status_code = 'o' THEN 'LIB USE ONLY'
WHEN i.item_status_code = 'n' THEN 'BILLED NOTPAID'
WHEN i.item_status_code = '$' THEN 'BILLED PAID'
WHEN i.item_status_code = 't' THEN 'IN TRANSIT'
WHEN i.item_status_code = '!' THEN 'ON HOLDSHELF'
WHEN i.item_status_code = 'l' THEN 'LOST'
-- At INN-Reach sites, the following additional codes and definitions are standard:
WHEN i.item_status_code = '@' THEN 'OFF SITE'
WHEN i.item_status_code = '#' THEN 'RECEIVED'
WHEN i.item_status_code = '%' THEN 'RETURNED'
WHEN i.item_status_code = '&' THEN 'REQUEST'
WHEN i.item_status_code = '_' THEN 'REREQUEST'
WHEN i.item_status_code = '(' THEN 'PAGED'
WHEN i.item_status_code = ')' THEN 'CANCELLED'
WHEN i.item_status_code = '1' THEN 'LOAN REQUESTED'
ELSE i.item_status_code
END
FROM
sierra_view.item_record as i
WHERE
i.record_id = r.id
LIMIT 1
)
ELSE NULL
END as item_record_status,
-- get the bib record id from holds (which can be item-level, volume-level, or bib-level)
CASE
WHEN r.record_type_code = 'i' THEN (
SELECT
l.bib_record_id
FROM
sierra_view.bib_record_item_record_link as l
WHERE
l.item_record_id = h.record_id
LIMIT 1
)
WHEN r.record_type_code = 'j' THEN (
SELECT
l.bib_record_id
FROM
sierra_view.bib_record_volume_record_link as l
WHERE
l.volume_record_id = h.record_id
LIMIT 1
)
WHEN r.record_type_code = 'b' THEN (
h.record_id
)
ELSE NULL
END as bib_record_id,
CASE
WHEN h.status = '0' THEN 'On hold'
WHEN h.status = 'b' THEN 'Bib hold ready for pickup.'
WHEN h.status = 'j' THEN 'Volume hold ready for pickup.'
WHEN h.status = 'i' THEN 'Item hold ready for pickup.'
WHEN h.status = 't' THEN 'Bib, item, or volume in transit to pickup location.'
END as hold_status,
h.*
FROM
sierra_view.hold as h
LEFT OUTER JOIN
sierra_view.record_metadata as r
ON
r.id = h.record_id
LEFT OUTER JOIN
sierra_view.patron_record as p
ON
p.record_id = h.patron_record_id
LEFT OUTER JOIN
sierra_view.patron_record_fullname as n
ON
n.patron_record_id = h.patron_record_id
WHERE
-- uncomment / comment out here to limit to INN-Reach / ILL holds
( is_ir IS true
OR is_ill IS true
)
;
-----
-----
SELECT
p.best_title,
p.publish_year,
t.*
FROM
temp_holds_data as t
JOIN
sierra_view.bib_record_property as p
ON
p.bib_record_id = t.bib_record_id