Circ Analysis By Copy Locations for York

create table rogan.peel as
select distinct acl.name from asset.copy_location acl
join actor.org_unit aou on aou.id = acl.owning_lib
join actor.org_unit org on org.id = aou.parent_ou
where org.shortname = 'YCL'
;

alter table rogan.peel add column blah text;

select * from rogan.peel;

update rogan.peel set blah = 'Non Circulating' where name in ('Young Adult Reference','Juvenile Reference','Shasta''s Desk','Periodical Storage','Book Club','Technology Center','Technical Services','Closed Stack','Reference','Reference Desk','Retired Reference','Atlas Table','Microfilm','kinder box','kinder box items','Infotrac Index','Infotrac Microfilm','Infotrac Full Text','Professional','Reference Desk','Ready Reference','Index Table');
update rogan.peel set blah = 'Periodicals' where name in ('Young Adult Periodicals','Browsing Periodicals','Juvenile Periodicals');
update rogan.peel set blah = 'Juvenile Fiction' where name in ('Juvenile Newbery Award','Juvenile Caldecott Award','Juvenile Paperbacks','Juvenile Read-Alongs','Juvenile Easy');
update rogan.peel set blah = 'Juvenile Video' where name in ('Juvenile Video Cassettes','Juvenile DVD');
update rogan.peel set blah = 'Spanish' where name in ('Juvenile Spanish','Adult Spanish');
update rogan.peel set blah = 'Audio Books' where name in ('Books On CD (Full-Length)','Books on CD (Abridged)','Books on Cassette (Abridged)','Books on Cassette (Full-Length)');
update rogan.peel set blah = 'Graphic Novels' where name in ('Young Adult Graphic Novel','Juvenile Graphic Novel');
update rogan.peel set blah = 'Adult Fiction' where name in ('On Display','New Books -- Adult Fiction','Paperbacks');
update rogan.peel set blah = 'Adult NonFiction' where name in ('New Books -- Adult Nonfiction');
update rogan.peel set blah = 'Downloadables' where name in ('Downloadable Audiobook','Juvenile Downloadable Audiobook','Downloadable E-Book','Young Adult Downloadable Audiobook');
update rogan.peel set blah = 'Adult Videos' where name in ('DVD','Video Cassettes');
update rogan.peel set blah = 'Large Print' where name in ('New Books -- Large Print');
update rogan.peel set blah = 'Special Collection' where name in ('Special Collection Fiction','Special Collection Nonfiction','Special Collection Periodicals','Juvenile Special Collection');
update rogan.peel set blah = 'Juvenile Nonfiction' where name in ('Class Project Table','Resources For Early Childhood');
update rogan.peel set blah = 'Juvenile Audiobooks' where name in ('Juvenile Books On Cassette','Juvenile Books On CD');
update rogan.peel set blah = 'Young Adult' where name in ('Young Adult Paperbacks');
update rogan.peel set blah = name where blah is null;


-- totals
select count(circ.id), peel.blah
from action.circulation circ
join actor.org_unit org1 on org1.id = circ.circ_lib
join actor.org_unit org2 on org2.id = org1.parent_ou
join asset.copy ac on ac.id = circ.target_copy
join asset.call_number call on call.id = ac.call_number
join asset.copy_location acl on acl.id = ac.location
join rogan.peel peel on peel.name = acl.name
where date(circ.xact_start) >= '2012-01-01' and date(circ.xact_start) <= '2012-12-31' and org2.shortname = 'YCL'
group by 2
order by 2 ASC
;


select distinct blah from rogan.peel;

-- new items
select count(ac.id), peel.blah
from asset.copy ac
join actor.org_unit org1 on org1.id = ac.circ_lib
join actor.org_unit org2 on org2.id = org1.parent_ou
join asset.call_number call on call.id = ac.call_number
join asset.copy_location acl on acl.id = ac.location
join rogan.peel peel on peel.name = acl.name
where date(ac.create_date) >= '2012-01-01' and date(ac.create_date) <= '2012-12-31' and org2.shortname = 'YCL'
group by 2
order by 2 ASC
;


-- monthl

select count(circ.id), extract(month from circ.xact_start), peel.blah
from action.circulation circ
join actor.org_unit org1 on org1.id = circ.circ_lib
join actor.org_unit org2 on org2.id = org1.parent_ou
join asset.copy ac on ac.id = circ.target_copy
join asset.call_number call on call.id = ac.call_number
join asset.copy_location acl on acl.id = ac.location
join rogan.peel peel on peel.name = acl.name
where date(circ.xact_start) >= '2012-01-01' and date(circ.xact_start) <= '2012-12-31' and org2.shortname = 'YCL'
group by 2, 3
order by 2, 3 ASC
;