Statistical Overview

select aou.id, aou.parent_ou, aou.shortname, aou.ou_type, aou.name, aoa.street1, aoa.street2, aoa.city, aoa.county, aoa.state, aoa.post_code
from actor.org_unit aou join actor.org_address aoa on aoa.id = aou.mailing_address;

select * from actor.org_unit_type;

select * from permission.grp_tree;

-- holds data
SELECT COUNT(ahr.id) AS holds, ahr.pickup_lib, ac.circ_lib AS source_lib,
ac.circ_modifier, acl.name as location, EXTRACT(MONTH FROM ahr.request_time) AS MONTH,
EXTRACT(YEAR FROM ahr.request_time) AS YEAR, au.profile as usr_profile
FROM ACTION.hold_request ahr
JOIN actor.usr au ON au.id = ahr.usr
JOIN asset.COPY ac ON ac.id = ahr.current_copy
JOIN asset.copy_location acl ON acl.id = ac.LOCATION
where ahr.fulfillment_time is not null
GROUP BY 2, 3, 4, 5, 6, 7, 8;

-- circ data
SELECT COUNT(acirc.id) AS circs, acirc.circ_lib, ac.circ_modifier, acl.name AS LOCATION,
EXTRACT(MONTH FROM acirc.xact_start) AS MONTH, EXTRACT(YEAR FROM acirc.xact_start) AS YEAR, au.profile AS usr_profile
FROM ACTION.circulation acirc
JOIN asset.COPY ac ON ac.id = acirc.target_copy
JOIN actor.org_unit aou ON aou.id = acirc.circ_lib
JOIN rogan.statistical_years y ON y.id = 6
JOIN asset.copy_location acl ON acl.id = ac.LOCATION
JOIN actor.usr au ON au.id = acirc.usr
GROUP BY 2, 3, 4, 5, 6, 7;