Summer Reading Stats

drop table if exists rogan.action_circulation;

/* create a table and map per circulation */

create table rogan.action_circulation as
select * from action.circulation where date(create_time) between '2015-06-01' and '2015-08-31';

alter table rogan.action_circulation add column print boolean default TRUE;

/* map print = true based on these circ mods */

/* then it got a lot more manual looking at shelving locations.  I made a review of a sample of five thousand shelving
location entries and based on that assumed that copies with shelving locations saying AUDIO or VIDEO or EQUIPMENT
should be changed to non-print then I re-ran it with exclusions and found more like DVD */

update rogan.action_circulation set print = false where target_copy in (
        select ac.id from asset.copy ac join rogan.action_circulation rcirc on rcirc.target_copy = ac.id
        join asset.copy_location acl on acl.id = ac.location
        where ac.circ_modifier in ('ELECTRONIC EQUIPMENT','VIDEO','AUDIO','EREADER','PLAYAWAY','AUDIO BOOK',
        'THEME KIT','EQUIPMENT','KIT','INTERNET','GAME','LEASE DVD')
        or (acl.name ilike '%audio%' or acl.name ilike '%video%' or acl.name ilike '%equipment%' or acl.name ilike '%dvd%'
        or acl.name ilike '%books on cd%' or acl.name ilike '%books on cassette%' or acl.name ilike '%music cd%' or
        acl.name ilike '%adult 360%' or acl.name ilike '%adult ps2%')
);

/* now I will go through all distinct occurances of the library systems and these circ mods with other shelving
location names excluding those that had audio, video or unknown in the name */

select DISTINCT aou.parent_ou, ac.circ_modifier, acl.name
from asset.copy ac
join actor.org_unit aou on aou.id = ac.circ_lib
join asset.copy_location acl on acl.id = ac.location
where ac.circ_modifier in ('21 DAY LEASE','14 DAY LEASE','7 DAY LEASE','28 DAY','14 DAY','21 DAY','7 DAY','RENTAL')
and acl.name not ilike '%audio%' and acl.name not ilike '%audio%' and acl.name not ilike '%video%'
and acl.name not ilike '%equipment%' and acl.name not ilike '%dvd%' and acl.name not ilike '%books on cd%'
and acl.name not ilike '%books on cassette%' and acl.name not ilike '%music cd%' and
acl.name not ilike '%adult 360%' and acl.name not ilike '%adult ps2%'
order by 1, 2;

alter table rogan.action_circulation add column adult boolean default true;
alter table rogan.action_circulation add column ya boolean default false;
alter table rogan.action_circulation add column juv boolean default false;

update rogan.action_circulation set adult = false, ya = true where target_copy in (
        select ac.id from asset.copy ac join rogan.action_circulation rcirc on rcirc.target_copy = ac.id
        join asset.copy_location acl on acl.id = ac.location
        where acl.name ilike '%young%'
);


update rogan.action_circulation set adult = false, juv = true where target_copy in (
        select ac.id from asset.copy ac join rogan.action_circulation rcirc on rcirc.target_copy = ac.id
        join asset.copy_location acl on acl.id = ac.location
        where acl.name ilike '%juv%'
);

SELECT juv_a.shortname as "Library",
juv_a.circs as "Juvenile Print Circs", juv_not_a.circs as "Juvenile Non-Print Circs",
juv_a.circs+juv_not_a.circs+coalesce(non.circs,0) as "Total Juvenile Circs",
ya_a.circs as "YA Print Circs", ya_not_a.circs as "YA Non-Print Circs",
ya_a.circs+ya_not_a.circs+coalesce(non.circs,0) as "Total YA Circs",
adult_a.circs as "Adult Print Circs", adult_not_a.circs as "Adult Non-Print Circs",
adult_a.circs+adult_not_a.circs+coalesce(non.circs,0) as "Total Adult Circs"
FROM (
        SELECT COUNT(circ.id) AS circs, org2.shortname AS shortname
        FROM rogan.action_circulation circ
        JOIN actor.org_unit org ON org.id = circ.circ_lib
        JOIN actor.org_unit org2 ON org2.id = org.parent_ou
        WHERE circ.print = true and circ.juv = true
        GROUP BY 2
) juv_a
left join (
        SELECT COUNT(circ.id) AS circs, org2.shortname AS shortname
        FROM rogan.action_circulation circ
        JOIN actor.org_unit org ON org.id = circ.circ_lib
        JOIN actor.org_unit org2 ON org2.id = org.parent_ou
        WHERE circ.print = true and circ.adult = true
        GROUP BY 2
) adult_a on adult_a.shortname = juv_a.shortname
left join (
        SELECT COUNT(circ.id) AS circs, org2.shortname AS shortname
        FROM rogan.action_circulation circ
        JOIN actor.org_unit org ON org.id = circ.circ_lib
        JOIN actor.org_unit org2 ON org2.id = org.parent_ou
        WHERE circ.print = false and circ.juv = true
        GROUP BY 2
) juv_not_a on juv_not_a.shortname = juv_a.shortname
left join (
        SELECT COUNT(circ.id) AS circs, org2.shortname AS shortname
        FROM rogan.action_circulation circ
        JOIN actor.org_unit org ON org.id = circ.circ_lib
        JOIN actor.org_unit org2 ON org2.id = org.parent_ou
        WHERE circ.print = false and circ.adult = true
        GROUP BY 2
) adult_not_a on adult_not_a.shortname = juv_a.shortname
left join (
        SELECT COUNT(circ.id) AS circs, org2.shortname AS shortname
        FROM rogan.action_circulation circ
        JOIN actor.org_unit org ON org.id = circ.circ_lib
        JOIN actor.org_unit org2 ON org2.id = org.parent_ou
        WHERE circ.print = true and circ.ya = true
        GROUP BY 2
) ya_a on ya_a.shortname = juv_a.shortname
left join (
        SELECT COUNT(circ.id) AS circs, org2.shortname AS shortname
        FROM rogan.action_circulation circ
        JOIN actor.org_unit org ON org.id = circ.circ_lib
        JOIN actor.org_unit org2 ON org2.id = org.parent_ou
        WHERE circ.print = false and circ.ya = true
        GROUP BY 2
) ya_not_a on ya_not_a.shortname = juv_a.shortname
left join (  SELECT coalesce(count(circ.id),0) AS circs, org2.shortname AS shortname
        FROM action.non_cataloged_circulation circ
        left JOIN actor.org_unit org ON org.id = circ.circ_lib
        JOIN actor.org_unit org2 ON org2.id = org.parent_ou
        where date(circ.circ_time) >= '2015-06-01' AND date(circ.circ_time) <= '2015-08-31'
        GROUP BY 2
) non on non.shortname = juv_a.shortname
;