Statistics - Holds

/* redoing old work in a cleaner way, Feb 2015

holds have different aspects people want to look at so this is a bit more complicated by definition
I'm leanving out stats on frozen, canceled and expired holds as they overly complicate it

*/

CREATE TABLE rogan.stats_holds (
        id              serial,
        holds           INT,
        pickup_lib      INT,
        source_lib      INT,
        circ_modifier   TEXT,
        location        INT,
        stats_year      INT,
        month           INT,
        usr_profile     INT
);

/* let's pull how holds were fulfilled, do it by source library to distributed library */

insert into rogan.stats_holds (holds, pickup_lib, source_lib, circ_modifier, location, stats_year, month, usr_profile)
(
    select count(ahr.id) as holds, ahr.pickup_lib, ac.circ_lib as source_lib,
    ac.circ_modifier, acl.id as location, y.id as stats_year,
    extract(month from ahr.request_time) as month, 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
    JOIN rogan.statistical_years y ON y.id = 6
    WHERE ahr.fulfillment_time >= y.year_start AND ahr.fulfillment_time <= y.year_end
    group by 2, 3, 4, 5, 6, 7, 8
);


/* how many items did patrons get each year per library? can make it parents pretty easily or group by something like circ mods*/

select sum(holds), pickup_lib
from rogan.stats_holds rsh
where stats_year = 5
group by 2;

select sum(holds), aou.parent_ou
from rogan.stats_holds rsh
join actor.org_unit aou on aou.id = pickup_lib
where stats_year = 5
group by 2;

select sum(holds), aou.parent_ou, circ_modifier
from rogan.stats_holds rsh
join actor.org_unit aou on aou.id = pickup_lib
where stats_year = 5
group by 2, 3;


/* report 2 what holds did we fill? same as above but flip pickup for source*/

select sum(holds), source_lib
from rogan.stats_holds rsh
where stats_year = 5
group by 2;

/* report 3 what ICLs came in for this we take the what did we fill and see where parents of the source and pickup differ */

select sum(holds), pickup_lib
from rogan.stats_holds rsh
join actor.org_unit aou_pickup on aou_pickup.id = pickup_lib
join actor.org_unit aou_source on aou_source.id = source_lib
where stats_year = 5 and aou_pickup.parent_ou != aou_source.parent_ou
group by 2;


/* report 3 what ICLs came in for this we take the what did we fill and see where parents of the source and pickup differ */

select sum(holds), source_lib, pickup_lib
from rogan.stats_holds rsh
join actor.org_unit aou_pickup on aou_pickup.id = pickup_lib
join actor.org_unit aou_source on aou_source.id = source_lib
where stats_year = 5 and aou_pickup.parent_ou != aou_source.parent_ou
group by 2, 3;

-- lets make it county based and see who York gave stuff to
select sum(holds), aou_source.parent_ou as source, aou_pickup.parent_ou as pickup
from rogan.stats_holds rsh
join actor.org_unit aou_pickup on aou_pickup.id = pickup_lib
join actor.org_unit aou_source on aou_source.id = source_lib
where stats_year = 5 and aou_pickup.parent_ou != aou_source.parent_ou
and aou_source.parent_ou = 119
group by 2, 3;

-- lets make it county based and see who York got stuff from
select sum(holds), aou_source.parent_ou as source, aou_pickup.parent_ou as pickup
from rogan.stats_holds rsh
join actor.org_unit aou_pickup on aou_pickup.id = pickup_lib
join actor.org_unit aou_source on aou_source.id = source_lib
where stats_year = 5 and aou_pickup.parent_ou != aou_source.parent_ou
and aou_pickup.parent_ou = 119
group by 2, 3;