Stat Cat Cross Tab Report

stat-cat-cross-tab.sql

rollback;
begin;

select larl_temp.colpivot('_output',$$

select --acn.record
acp.id
, acp.barcode
, acp.circ_modifier
,ascc.name, asce.value
from asset.copy acp
join asset.call_number acn on acn.id=acp.call_number
join asset.stat_cat_entry_copy_map ascecm on ascecm.owning_copy=acp.id
join asset.stat_cat ascc on ascc.id=ascecm.stat_cat
join asset.stat_cat_entry asce on asce.id=ascecm.stat_cat_entry
where
acp.circ_modifier not in ('ILL','ILLspecial')
and not acp.deleted
order by 1,2
--limit 80000
$$, array['id','barcode','circ_modifier'], array['name'],'#.value', null);

--select * from _output
--order by 2,3,4,5,6;

insert into container.copy_bucket (name, owner, btype, description, pub)
select
 distinct concat_ws('-','StatCatReport',circ_modifier,"'FIC/NF'", "'Format'", "'Genre Book'", "'Genre Movie'", "'Genre Music'", "'Reading/Watching Level'", "'Special Collections'" , "'ZZ Old Item Type Code'", "'LGBTQ Collection'") as name
,1::bigint as owner
,'staff_client' as bytpe
,'Stat Cat Cross Tab Report' as description
,false as pub
from _output;

insert into container.copy_bucket_item (bucket, target_copy)
(select 
(select id from container.copy_bucket
where name=concat_ws('-','StatCatReport',circ_modifier,"'FIC/NF'", "'Format'", "'Genre Book'", "'Genre Movie'", "'Genre Music'", "'Reading/Watching Level'", "'Special Collections'" , "'ZZ Old Item Type Code'", "'LGBTQ Collection'")
and owner=1) as bucket
,o.id
from _output o);

create table larl_temp.stat_cat_summary as
select count(barcode), circ_modifier, "'FIC/NF'", "'Format'", "'Genre Book'", "'Genre Movie'", "'Genre Music'", "'Reading/Watching Level'", "'Special Collections'" , "'ZZ Old Item Type Code'", "'LGBTQ Collection'"--
, string_agg(barcode,', ') Barcodes
, concat_ws('-','StatCatReport',circ_modifier,"'FIC/NF'", "'Format'", "'Genre Book'", "'Genre Movie'", "'Genre Music'", "'Reading/Watching Level'", "'Special Collections'" , "'ZZ Old Item Type Code'", "'LGBTQ Collection'") Bucket_name
, (select id from container.copy_bucket
where name=concat_ws('-','StatCatReport',circ_modifier,"'FIC/NF'", "'Format'", "'Genre Book'", "'Genre Movie'", "'Genre Music'", "'Reading/Watching Level'", "'Special Collections'" , "'ZZ Old Item Type Code'", "'LGBTQ Collection'")
and owner=1) as bucket_id
from _output
--where
--"'ZZ Old Item Type Code'"~'L$'
group by 2,3,4,5,6,7,8,9,10,11
--having count(barcode)>5
order by 2,3,4,5,6;

select * from larl_temp.stat_cat_summary
where
count >20
order by 2,3,4,5,6
;

select * from larl_temp.stat_cat_summary
where
count <=20
order by 2,3,4,5,6
;

commit;

rollback;
begin;

select larl_temp.colpivot('_output',$$
select --acn.record
acp.id
, acp.barcode
, acp.circ_modifier
, acnp.label::text as cn_prefix
, icon.value::text as bib_icons
,ascc.name, asce.value
from asset.copy acp
join asset.call_number acn on acn.id=acp.call_number
left outer join asset.call_number_prefix acnp on acnp.id=acn.prefix
join (select id,string_agg(value,', ' order by value) as value from metabib.record_attr_flat where attr='icon_format'
group by 1) as icon on icon.id=acn.record
join asset.stat_cat_entry_copy_map ascecm on ascecm.owning_copy=acp.id
join asset.stat_cat ascc on ascc.id=ascecm.stat_cat
join asset.stat_cat_entry asce on asce.id=ascecm.stat_cat_entry
where
acp.circ_modifier not in ('ILL','ILLspecial')
and not acp.deleted
order by 1,2
--limit 30000
$$, array['id','barcode','circ_modifier','cn_prefix','bib_icons'], array['name'],'#.value', null);

select count(barcode), circ_modifier, cn_prefix, bib_icons, "'FIC/NF'", "'Format'", "'Genre Book'", "'Genre Movie'", "'Genre Music'", "'Reading/Watching Level'", "'Special Collections'" , "'ZZ Old Item Type Code'", "'LGBTQ Collection'"--
, string_agg(barcode,', ') Barcodes
, concat_ws('-','StatCatReport',circ_modifier,"'FIC/NF'", "'Format'", "'Genre Book'", "'Genre Movie'", "'Genre Music'", "'Reading/Watching Level'", "'Special Collections'" , "'ZZ Old Item Type Code'", "'LGBTQ Collection'") Bucket_name
, (select id from container.copy_bucket
where name=concat_ws('-','StatCatReport',circ_modifier,"'FIC/NF'", "'Format'", "'Genre Book'", "'Genre Movie'", "'Genre Music'", "'Reading/Watching Level'", "'Special Collections'" , "'ZZ Old Item Type Code'", "'LGBTQ Collection'")
and owner=1) as bucket_id
from _output
--where
--"'ZZ Old Item Type Code'"~'L$'
group by 2,3,4,5,6,7,8,9,10,11,12,13
--having count(barcode)>5
order by 2,3,4,5,6,7,8,9,10;

rollback;