stompro

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"

stompro

-- 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 

stompro

-- 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'),

stompro

-- 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
;

stompro

-- 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

stompro

-- 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$

stompro

-- 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;

stompro

-- 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
;

stompro

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;

stompro

-- 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
;