Items - plch/sierra-sql GitHub Wiki
Finding unique items at a specific branch across the entire system (REDUX)
with all_branches as (
select
rm.id as bib_record_id,
bn.name as branch_name
from
sierra_db.sierra_view.record_metadata rm
join sierra_db.sierra_view.bib_record_item_record_link brirl on brirl.bib_record_id = rm.id
join sierra_db.sierra_view.item_record ir on (
ir.record_id = brirl.item_record_id
and ir.item_status_code in (
-- consider these status codes to be "active"
'-', '!', 'b', 'p', '(', '@', ')', '_', '=', '+', 't'
)
)
join sierra_db.sierra_view."location" l on l.code = ir.location_code
join sierra_db.sierra_view.branch b on b.code_num = l.branch_code_num
join sierra_db.sierra_view.branch_name bn on bn.branch_id = b.id
where
rm.record_type_code = 'b'
and rm.campus_code = ''
group by
rm.id, bn.name
),
branch_counts as (
select
bib_record_id,
count(distinct branch_name) as branch_count
from
all_branches
group by
bib_record_id
),
exclusive_titles as (
select
ab.bib_record_id,
ab.branch_name
from
all_branches ab
join branch_counts bc on ab.bib_record_id = bc.bib_record_id
where
bc.branch_count = 1
)
select
-- exclusive_titles.bib_record_id,
branch_name,
rmb.record_num as bib_record_num,
rmi.record_num as item_record_num,
ir.location_code,
ir.last_checkout_gmt,
upper( irp.call_number_norm) as call_number,
irp.barcode
from
exclusive_titles
join sierra_db.sierra_view.bib_record_item_record_link brirl on brirl.bib_record_id = exclusive_titles.bib_record_id
join sierra_db.sierra_view.item_record ir on (
ir.record_id = brirl.item_record_id
and ir.item_status_code in (
-- consider these status codes to be "active"
'-', '!', 'b', 'p', '(', '@', ')', '_', '=', '+', 't'
)
)
join sierra_db.sierra_view.item_record_property irp on irp.item_record_id = ir.record_id
join sierra_db.sierra_view.record_metadata rmb on rmb.id = exclusive_titles.bib_record_id
join sierra_db.sierra_view.record_metadata rmi on rmi.id = ir.record_id
where
branch_name = '{}'
order by
location_code,
call_number_norm,
last_checkout_gmt
https://gist.github.com/rayvoelker/9f72b85109e1990462e6d07df439cf07
Find duplicate item barcodes in the system
with dupe_barcode as (
select
v.field_content as barcode,
date(max(rm.creation_date_gmt)) as latest_create_date
from
sierra_view.varfield as v
join sierra_view.record_metadata rm on (
rm.id = v.record_id
and rm.record_type_code = 'i'
and rm.campus_code = ''
)
where
v.varfield_type_code = 'b' -- barcode
group by
v.field_content
having
count(v.field_content) > 1
)
select
--barcode num_occurrences item_record_num icode1 itype_code_num title item_created item_updated
dense_rank() over (order by db.latest_create_date desc, db.barcode) as barcode_group_num,
db.barcode,
rmi.record_type_code || rmi.record_num || 'a' as item_record_num,
ir.icode1,
ir.itype_code_num,
brp.best_title as title,
date(rmi.creation_date_gmt) item_created,
date(rmi.record_last_updated_gmt) as item_updated
from
dupe_barcode as db
join sierra_view.phrase_entry as pe on (
pe.index_tag || pe.index_entry = 'b' || db.barcode
)
left outer join sierra_view.item_record as ir on
ir.record_id = pe.record_id
left outer join sierra_view.record_metadata rmi on
rmi.id = ir.record_id
left outer join sierra_view.bib_record_item_record_link as brirl on
brirl.item_record_id = ir.record_id
left outer join sierra_view.bib_record_property as brp on
brp.bib_record_id = brirl.bib_record_id
order by
db.latest_create_date desc,
db.barcode
output example:
| barcode_group_num | barcode | item_record_num | icode1 | itype_code_num | title | item_created | item_updated |
|---|---|---|---|---|---|---|---|
| 1 | 0989897357020 | i12201355a | 0 | 46 | Dictionaium britannicum : or, A more compleat universal etymological English dictionary than any extant. Containing not only the words, and their explication; but their etymologies. Also explaining hard and technical words. Illustrated with near five hundred cuts. Likewise a collection and explanation of words and phrases us'd in our antient charters, statutes, writs. Also the theogony, theology, and mythology of the Egyptians, Greek, Romans, etc. To which is addes, a collection of proper names of persons and places in Great Britain. | 2023-09-20 | 2023-09-20 |
| 1 | 0989897357020 | i2360989a | 0 | 46 | Dictionarium Britannicum : or a more compleat universal etymological English dictionary than any extant. Containing not only the words, and their explication; but their etymologies ... Also explaining hard and technical words ... Illustrated with near five hundred cuts ... Likewise a collection and explanation of words and phrases us'd in our antient charters, statutes, writs ... Also the ... mythology of the Egyptians, Greeks, Romans, etc. ... To which is added, a collection of proper names of persons and places in Great-Britain ... | 2012-06-28 | 2019-04-03 |
| 2 | 0984494757011 | i2963339a | 0 | 46 | Merchant vessels of the United States. | 2012-06-29 | 2019-04-03 |
| 2 | 0984494757011 | i12153929a | 0 | 46 | Merchant vessels of the United States. | 2023-08-08 | 2023-08-08 |
| 3 | 2nanf | i11769400a | 0 | 0 | Beyond the darkness : a gentle guide for living with grief & thriving after loss | 2022-08-23 | 2023-03-03 |
| 3 | 2nanf | i11650937a | 0 | 0 | The dark queens : the bloody rivalry that forged the medieval world | 2022-05-13 | 2023-11-01 |
| 3 | 2nanf | i11670486a | 0 | 0 | Adriatic : a concert of civilizations at the end of the modern age | 2022-05-31 | 2023-10-22 |
| 3 | 2nanf | i11706056a | 0 | 0 | Black boy smile : a memoir in moments | 2022-06-29 | 2023-05-15 |
| 3 | 2nanf | i11648748a | 0 | 0 | When a child dies : a hopeful healing guide for surviving the loss of a child | 2022-05-12 | 2023-02-24 |
| 3 | 2nanf | i11673202a | 0 | 0 | Fly girl : a memoir | 2022-06-02 | 2023-07-05 |
| 3 | 2nanf | i11669292a | 0 | 0 | The premonitions bureau : a true account of death foretold | 2022-05-31 | 2023-05-22 |
| 3 | 2nanf | i11778500a | 0 | 0 | 50 philosophy classics : your shortcut to the most important ideas on being, truth, and meaning | 2022-09-01 | 2023-11-01 |
| 3 | 2nanf | i11808412a | 0 | 0 | Prisoners of the castle : an epic story of survival and escape from Colditz, the Nazis' fortress prison | 2022-09-23 | 2023-09-11 |
| 3 | 2nanf | i11667061a | 0 | 0 | Damn Lucky : one man's courage during the bloodiest military campaign in aviation history | 2022-05-26 | 2023-02-20 |
| 3 | 2nanf | i11663224a | 0 | 0 | The world according to color : a cultural history | 2022-05-24 | 2023-11-09 |
| 3 | 2nanf | i11821306a | 0 | 0 | The pain we carry : healing from complex PTSD for people of color : reclaiming wholeness despite the burdens of systemic, intergenerational, and attachment trauma | 2022-10-03 | 2023-10-03 |
| 3 | 2nanf | i11622266a | 0 | 0 | Teaching kids to read : embracing guided reading in primary school classrooms | 2022-04-21 | 2023-07-25 |
| 3 | 2nanf | i11729562a | 0 | 0 | Rickey : the life and legend of an American original | 2022-07-22 | 2023-02-20 |
| 3 | 2nanf | i11623795a | 0 | 0 | Accessible vacations : an insider's guide to 10 national parks | 2022-04-22 | 2023-10-20 |
| 3 | 2nanf | i11625503a | 0 | 0 | Living untethered : beyond the human predicament | 2022-04-25 | 2023-11-05 |
| 3 | 2nanf | i11627556a | 0 | 0 | The worth of water : our story of chasing solutions to the world's greatest challenge | 2022-04-26 | 2023-02-20 |
Finding unique items at a specific branch across the entire system
# this is a work in progress, but should get general enough data
# ... change the branch_code_num to target the branch needed
with bibs_unique_items as (
select
rm.id as bib_record_id
from
sierra_view.record_metadata as rm
join sierra_view.bib_record_item_record_link as l on l.bib_record_id = rm.id
join sierra_view.item_record ir on ir.record_id = l.item_record_id
where
rm.record_type_code = 'b'
and rm.campus_code = ''
and ir.item_status_code = '-'
group by 1
having count(ir.id) = 1
)
select
(
select
'b' || rm.record_num || 'a'
from
sierra_view.record_metadata as rm
where
rm.id = b.bib_record_id
) as bib_record_num,
ir2.location_code,
p.call_number,
p.barcode
from
bibs_unique_items as b
join sierra_view.bib_record_property as brp on brp.bib_record_id = b.bib_record_id
join sierra_view.bib_record_item_record_link as l on l.bib_record_id = b.bib_record_id
join sierra_view.item_record as ir2 on ir2.record_id = l.item_record_id
join sierra_view."location" as loc on loc.code = ir2.location_code
join sierra_view.item_record_property as p on p.item_record_id = ir2.record_id
where loc.branch_code_num = 9
and item_status_code = '-'
More granular "Claimed returned" search / report
DROP TABLE IF EXISTS temp_claims_returned;
--
CREATE TEMP TABLE temp_claims_returned AS
SELECT
r.record_type_code || r.record_num || 'a' as item_record_num,
(
SELECT
string_agg(v.field_content, ',' order by v.occ_num)
FROM
sierra_view.varfield as v
WHERE
v.record_id = r.id
AND v.varfield_type_code = 'b'
) AS item_barcodes,
substring(v.field_content, '^.{15}')::date as claimed_date,
v.record_id,
v.field_content
FROM
sierra_view.varfield as v
JOIN
sierra_view.record_metadata as r
ON
(r.id = v.record_id)
WHERE
v.varfield_type_code = 'x'
AND v.marc_tag is null
AND v.field_content ~ '^.{17}Claimed returned'
AND r.record_type_code = 'i'
;
---
ANALYSE temp_claims_returned;
---
-- produce our results
SELECT
*
FROM
temp_claims_returned as c
WHERE
c.claimed_date >= ( (DATE_TRUNC('month', NOW()) - INTERVAL '5 years 1 month') )::date -- first day of 5 years and 1 month ago
AND c.claimed_date < ( (DATE_TRUNC('month', NOW())) - INTERVAL '5 years' )::date -- last day of 5 years and 1 month ago
;
---
-- -- get the count of claims returned from each year
-- SELECT
-- extract(year from c.claimed_date) AS claimed_year,
-- count(*)
--
-- FROM
-- temp_claims_returned as c
--
-- GROUP BY
-- claimed_year
--
-- ORDER BY
-- claimed_year
Find cases where an item record is linked to more than one bib record (improved)
--- find same item record linked to multiple bib records
SELECT
r.record_type_code || r.record_num || 'a' as bib_record_num,
ir.record_type_code || ir.record_num || 'a' as item_record_num
FROM
sierra_view.record_metadata as r
JOIN
sierra_view.bib_record_item_record_link as l
ON
l.bib_record_id = r.id
JOIN
sierra_view.record_metadata as ir
ON
ir.id = l.item_record_id
WHERE
l.item_record_id IN (
SELECT
l.item_record_id
-- ,count(item_record_id)
FROM
sierra_view.bib_record_item_record_link as l
GROUP BY
l.item_record_id
HAVING
count(item_record_id) > 1
);
Find cases where an item record is linked to more than one bib record
-- find item records that are linked to more than one bib record
SELECT
l.item_record_id,
count(bib_record_id)
FROM
sierra_view.bib_record_item_record_link as l
GROUP BY
l.item_record_id
HAVING
count(bib_record_id) > 1
Count number of items by criteria
SELECT
-- i.agency_code_num,
p.name as item_agency,
count(*)
FROM
sierra_view.item_record as i
JOIN
sierra_view.agency_property_myuser as p
ON
p.code = i.agency_code_num
WHERE
i.item_status_code IN (
'-',
'o',
'r',
't',
'!'
)
AND substring(i.location_code,1,2) NOT IN (
'vi',
'yt',
'ym',
'yc',
'yh'
)
GROUP BY
p.name,
p.display_order
ORDER BY
p.display_order
Items in Repair Too Long
select
irm.record_num as item_record_num,
brm.record_num as bib_record_num,
irp.call_number_norm,
irp.barcode,
brp.best_author,
brp.best_title,
brp.publish_year,
ir.itype_code_num,
ipn."name" as itype_name,
-- ir.location_code,
-- locn.name as location_name,
-- bn.name as branch_name,
ir.agency_code_num,
ir.is_suppressed,
my_loc.*
from
-- item data
sierra_view.item_record as ir
join sierra_view.record_metadata as irm on irm.id = ir.record_id
join sierra_view.item_record_property as irp on irp.item_record_id = ir.record_id
join sierra_view.itype_property as itype_p on itype_p.code_num = ir.itype_code_num
join sierra_view.itype_property_name as ipn on ipn.itype_property_id = itype_p.id --
-- bib data
join sierra_view.bib_record_item_record_link as l on l.item_record_id = ir.record_id -- join sierra_view.bib_record as br
-- on br.record_id = l.bib_record_id
join sierra_view.record_metadata as brm on brm.id = l.bib_record_id
join sierra_view.bib_record_property as brp on brp.bib_record_id = l.bib_record_id --
-- location data
-- join sierra_view."location" as loc
-- on loc.code = ir.location_code
-- join sierra_view.location_name as locn
-- on locn.location_id = loc.id
-- join sierra_view.branch as b
-- on b.code_num = loc.branch_code_num
-- join sierra_view.branch_name as bn
-- on bn.branch_id = b.id
join (
-- https://github.com/plch/sierra-sql/wiki/Location#create-custom-sub-branch-names----useful-for-the-main-library-that-has
select
loc.code as location_code,
ln.name as location_name,
case
when loc.code in ('1') then 'Main Library - Cleanup'
when (
length(loc.code) > 1
and substr(loc.code, 1, 2) in ('4c', '1z', 'zz', 'yx')
) then 'Main Library - Cleanup'
when (
length(loc.code) > 1
and substr(loc.code, 1, 2) in ('1h')
) then 'Main Library - Homework Help'
when (
length(loc.code) > 1
and substr(loc.code, 1, 2) in ('3c', '3d', '3e', '3g')
) then 'Main Library - Genealogy and Local History'
when (
length(loc.code) > 1
and substr(loc.code, 1, 2) in ('no')
and ln.name NOT LIKE '~%' -- filter out virtual locations
) then 'Main Library - Unknown'
when (
length(loc.code) > 1
and substr(loc.code, 1, 2) in (
'1l',
'1c',
'1p',
'1f',
'2e',
'2g',
'2k',
'2m',
'2n',
'2r',
'2s',
'2t',
'3a',
'3h',
'3l',
'3r',
'4d'
)
) then 'Main Library - Popular Library'
else bn.name
end as sub_branch_name,
loc.is_public,
loc.is_requestable
from
sierra_view."location" as loc
join sierra_view.location_name as ln on ln.location_id = loc.id
join sierra_view.branch as b on b.code_num = loc.branch_code_num
join sierra_view.branch_name as bn on bn.branch_id = b.id
) as my_loc on my_loc.location_code = ir.location_code
where
ir.item_status_code = 'r'
and irm.record_last_updated_gmt <= now() - interval '60 days'
--order by
-- branch_name asc,
-- location_name asc