Purchase Alert Report - High Demand Holds Evergreen

LARL purchase alert.sql

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"
||' 30_Days:'||hold_counts."New Holds last 30 days"||' Frozen:'||hold_counts."Frozen Holds" as "Hold Counts"
-- other copy counts per bib & selected circ_lib
,copycounts.express
,copycounts."LongLost"
,copycounts."ShortLost"
,copycounts.Missing
,copycounts.Damaged
,copycounts.OnOrder
from
action.hold_request ahr
JOIN reporter.hold_request_record rhrr USING (id)
join actor.usr au on (ahr.usr=au.id and au.home_ou IN (SELECT id FROM actor.org_unit_descendants(101)))
LEFT JOIN action.hold_copy_map ahcm ON (ahr.id = ahcm.hold)
LEFT JOIN asset.copy acp ON (ahcm.target_copy = acp.id and acp.circ_lib in (SELECT id FROM actor.org_unit_descendants(101) ))
LEFT JOIN asset.call_number acn on (acn.id=acp.call_number)
left join (select rhrr.bib_record,
 max(case when ahr.frozen=false then age(now(),ahr.request_time) end) as "Oldest Hold"
,avg(case when ahr.frozen=false then age(now(),ahr.request_time) end) as "Average Age"
,sum(case when ahr.request_time >= now()-'7 days'::interval and ahr.frozen=false then 1 else 0 end) as "New Holds last 7 days"
,sum(case when ahr.request_time >= now()-'30 days'::interval and ahr.frozen=false then 1 else 0 end) as "New Holds last 30 days"
,sum(case when ahr.frozen=true then 1 else 0 end) as "Frozen Holds"
from
action.hold_request ahr
JOIN reporter.hold_request_record rhrr USING (id)
join actor.usr au on (ahr.usr=au.id and au.home_ou IN (SELECT id FROM actor.org_unit_descendants(101)))
where ahr.cancel_time IS NULL
AND ahr.fulfillment_time IS NULL
group by rhrr.bib_record
) hold_counts on hold_counts.bib_record=rhrr.bib_record
left outer join 
(select bre.id
 ,sum(case when acp.location=121 and acp.status in (0,1,5,6,7,8,9) then 1 else 0 end) as Express
 ,sum(case when acp.status in (3) and acp.status_changed_time <= now()-'30 days'::interval then 1 else 0 end) as "LongLost"
 ,sum(case when acp.status in (3) and acp.status_changed_time > now()-'30 days'::interval then 1 else 0 end) as "ShortLost"
 ,sum(case when acp.status in (4) then 1 else 0 end) as Missing
 ,sum(case when acp.status in (14) then 1 else 0 end) as Damaged
 ,sum(case when acp.status in (9) then 1 else 0 end) as OnOrder
  from asset.copy acp
  join asset.call_number acn on acp.call_number=acn.id
  join biblio.record_entry bre on bre.id=acn.record
  where
  acp.circ_lib in (SELECT id FROM actor.org_unit_descendants(101))
  and acp.deleted=false
  and bre.id in (select bib_record from reporter.hold_request_record rhrr join action.hold_request ahr on (ahr.id=rhrr.id) where ahr.cancel_time IS NULL
AND ahr.fulfillment_time IS NULL
and ahr.frozen = false
group by bib_record)
group by bre.id
  ) copycounts on (copycounts.id=rhrr.bib_record)
WHERE ahr.cancel_time IS NULL
AND ahr.fulfillment_time IS NULL
and ahr.frozen = false
and (acn.record=rhrr.bib_record or acn.record is null)
group by 1,2,6,7,8,9,10,11,12
having count(distinct ahr.id) >= 4
) x
join reporter.super_simple_record rssr on (x.bib_record=rssr.id)
where
(x.hold_copy_ratio>4.5 or x.hold_copy_ratio='Infinity'::float)
and x.holdable_copies+coalesce(x.express,0) < 10
order by x.type,rssr.author, rssr.title;

NWRL Purchase Alert.sql

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
, x.summary as "Hold Pickup Summary"
 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"
||' 30_Days:'||hold_counts."New Holds last 30 days"||' Frozen:'||hold_counts."Frozen Holds" as "Hold Counts"
,lib_counts.summary
-- other copy counts per bib & selected circ_lib
,copycounts.express
,copycounts."LongLost"
,copycounts."ShortLost"
,copycounts.Missing
,copycounts.Damaged
,copycounts.OnOrder
from
action.hold_request ahr
JOIN reporter.hold_request_record rhrr USING (id)
join actor.usr au on (ahr.usr=au.id and au.home_ou IN (SELECT id FROM actor.org_unit_descendants(127)))
LEFT JOIN action.hold_copy_map ahcm ON (ahr.id = ahcm.hold)
LEFT JOIN asset.copy acp ON (ahcm.target_copy = acp.id and acp.circ_lib in (SELECT id FROM actor.org_unit_descendants(127) ))
LEFT JOIN asset.call_number acn on (acn.id=acp.call_number)

left join (select rhrr.bib_record,
 max(case when ahr.frozen=false then age(now(),ahr.request_time) end) as "Oldest Hold"
,avg(case when ahr.frozen=false then age(now(),ahr.request_time) end) as "Average Age"
,sum(case when ahr.request_time >= now()-'7_days'::interval and ahr.frozen=false then 1 else 0 end) as "New Holds last 7 days"
,sum(case when ahr.request_time >= now()-'30_days'::interval and ahr.frozen=false then 1 else 0 end) as "New Holds last 30 days"
,sum(case when ahr.frozen=true then 1 else 0 end) as "Frozen Holds"
from
action.hold_request ahr
JOIN reporter.hold_request_record rhrr USING (id)
join actor.usr au on (ahr.usr=au.id and au.home_ou IN (SELECT id FROM actor.org_unit_descendants(127)))
where ahr.cancel_time IS NULL
AND ahr.fulfillment_time IS NULL
group by rhrr.bib_record
) hold_counts on hold_counts.bib_record=rhrr.bib_record

-- Count and list of pickup libs.
left join (
  select 
  z.bib_record,
  string_agg(z.summary,', ' order by z.summary) as summary
  from (
    select
    rhrr.bib_record, aou.shortname||':('||count(aou.shortname)||')' as summary
    from
    action.hold_request ahr
    JOIN reporter.hold_request_record rhrr USING (id)
    join actor.org_unit aou on ahr.pickup_lib=aou.id
    join actor.usr au on (ahr.usr=au.id and au.home_ou IN (SELECT id FROM actor.org_unit_descendants(127)))
    where ahr.cancel_time IS NULL
    AND ahr.fulfillment_time IS NULL
    and ahr.frozen=false
    group by rhrr.bib_record,aou.shortname
  ) z
  group by z.bib_record
) lib_counts on lib_counts.bib_record=rhrr.bib_record

left outer join 
(select bre.id
 ,sum(case when acp.location=121 and acp.status in (0,1,5,6,7,8,9) then 1 else 0 end) as Express
 ,sum(case when acp.status in (3) and acp.status_changed_time <= now()-'30 days'::interval then 1 else 0 end) as "LongLost"
 ,sum(case when acp.status in (3) and acp.status_changed_time > now()-'30 days'::interval then 1 else 0 end) as "ShortLost"
 ,sum(case when acp.status in (4) then 1 else 0 end) as Missing
 ,sum(case when acp.status in (14) then 1 else 0 end) as Damaged
 ,sum(case when acp.status in (9) then 1 else 0 end) as OnOrder
  from asset.copy acp
  join asset.call_number acn on acp.call_number=acn.id
  join biblio.record_entry bre on bre.id=acn.record
  where
  acp.circ_lib in (SELECT id FROM actor.org_unit_descendants(127))
  and acp.deleted=false
  and bre.id in (select bib_record from reporter.hold_request_record rhrr join action.hold_request ahr on (ahr.id=rhrr.id) where ahr.cancel_time IS NULL
AND ahr.fulfillment_time IS NULL
and ahr.frozen = false
group by bib_record)
group by bre.id
  ) copycounts on (copycounts.id=rhrr.bib_record)
WHERE ahr.cancel_time IS NULL
AND ahr.fulfillment_time IS NULL
and ahr.frozen = false
and (acn.record=rhrr.bib_record or acn.record is null)
group by 1,2,6,7,8,9,10,11,12,13
having count(distinct ahr.id) >= 4
) x
join reporter.super_simple_record rssr on (x.bib_record=rssr.id)
where
(x.hold_copy_ratio>4 or x.hold_copy_ratio='Infinity'::float)
-- and x.holdable_copies+coalesce(x.express,0) < 10
order by x.type,rssr.author, rssr.title