This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
select row_number() over (order by x.type,rssr.author, rssr.title) as num | |
, rssr.title, rssr.author | |
, x.bib_record, x.type, x.holdable_copies, x.Express, x.holdable_copies+x.express as "Total Copies" | |
, x.holds, x.hold_copy_ratio, x."Hold Counts", x."LongLost", x."ShortLost", x.missing, x.damaged, x.onorder | |
from ( | |
select rhrr.bib_record | |
,(select value from metabib.record_attr_flat where attr='icon_format' and id=rhrr.bib_record limit 1) as Type | |
, count(distinct acp.id) holdable_copies, count(distinct ahr.id) holds | |
,CASE WHEN (count(distinct acp.id)+coalesce(copycounts.express,0)) = 0 THEN 'Infinity'::FLOAT ELSE trunc((count(distinct ahr.id)::FLOAT/(count(distinct acp.id)+coalesce(copycounts.express,0))::FLOAT)::numeric,2) END AS hold_copy_ratio | |
,'Oldest:('||date_trunc('day',hold_counts."Oldest Hold")||') Average:('||date_trunc('day',hold_counts."Average Age")||') 7_Days:'||hold_counts."New Holds last 7 days" |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- Delete entries from auditor.asset_copy_history where the copy already has over | |
-- x number of newer versions. | |
begin; | |
-- Use window fuction to number each row in descending order, then choose | |
-- only the rows that are over a certain number. | |
with ranked_entries as | |
( | |
select |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- Look for addresses with states that are not abbreviated but could be. | |
-- Months and Abbreviations | |
WITH MonthCodes (month, code) | |
AS | |
( | |
SELECT month, code | |
FROM ( | |
VALUES | |
('Alabama', 'AL'), | |
('Alaska', 'AK'), |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- Accidentally merged readers digest large print into regular readers digest. | |
-- Look up deleted bib records | |
select * | |
from | |
biblio.record_entry bre | |
where | |
marc~'Readers Digest' | |
and bre.deleted=true | |
; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- Stats on catalog logins compared with checkouts | |
select | |
aou.shortname, pgt.name, count(au.id) as "Total Users" | |
,count(ac2.id) as "Has Checked Out Since Migration" | |
,round((count(ac2.id)*100)::numeric/count(au.id),2)||'%' as percent1 | |
,count(aua.id) as "OPAC Login since migration" | |
-- ,round((count(aua.id)*100)::numeric/count(ac2.id),2)||'%' as percent2 | |
,count(ac1.id) as "Checkout in last month" | |
,count(aua2.id) as "OPAC Login in last month" | |
from |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- This version supports moving a call number to a new prefix | |
-- Move volume to new volume name on same bib. Used to rename volumes that get entered | |
-- incorrectly. | |
-- If there is already a volume with the same name, move copies into it. Otherwize rename it. | |
-- Credit to tsbere for showing me the way with his parts moving function. | |
-- Update 10-29-2018 - excluded deleted volumes from search for existing volumes. | |
CREATE OR REPLACE FUNCTION evergreen.larl_moveto_volume(volume_id bigint, new_label text, new_prefix_id bigint) | |
RETURNS void | |
LANGUAGE plpgsql | |
AS $function$ |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- Report on which items don't have circ as type set. | |
select aou.shortname, au.usrname as editor,ac.circ_modifier, ac.barcode, ac.edit_date, ac.circ_lib | |
from asset.copy ac | |
join actor.usr au on au.id=ac.editor | |
join actor.org_unit aou on aou.id=ac.circ_lib | |
where ac.deleted=false | |
and ac.circ_as_type is null | |
-- and ac.status!=9 | |
and ac.circ_modifier is not null | |
order by aou.shortname, ac.edit_date; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- Magazines to delete | |
select acp.barcode, acp.create_date, aou.shortname | |
from asset.copy acp | |
join actor.org_unit aou on aou.id=acp.circ_lib | |
where acp.status=13 | |
and acp.circ_modifier='Magazine' | |
and acp.deleted=false | |
order by acp.circ_lib | |
; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Need to delete old false records from audit tables. | |
-- Count of bib audit records before a certain date. | |
select count(audit_id) from auditor.biblio_record_entry_lifecycle where audit_time<'2015-10-17'::timestamp; | |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- List counts of copy statuses by circ lib | |
select aou.shortname,cs.name as status,count(ac.id),now() | |
from asset.copy ac | |
join config.copy_status cs on (ac.status=cs.id) | |
join actor.org_unit aou on aou.id=ac.circ_lib | |
where ac.deleted=false | |
-- and ac.circ_lib in (SELECT id FROM actor.org_unit_descendants(127)) | |
group by aou.shortname,cs.name | |
order by aou.shortname,cs.name | |
; |