Transits Sent and Received

create table rogan.sent as
select count(atc.id) as "sent count", parent.shortname, extract(month from atc.source_send_time) as month
from action.transit_copy atc
left join actor.org_unit aou on aou.id = atc.source
join actor.org_unit parent on parent.id = aou.parent_ou
where
(extract(year from atc.source_send_time) = '2014' and extract(month from atc.source_send_time) in (7,8,9,10,11,12))
or
(extract(year from atc.source_send_time) = '2015' and extract(month from atc.source_send_time) in (1,2,3,4,5,6))
group by 2, 3
order by 2, 3
;

alter table rogan.sent add column s int;
update rogan.sent set s = 1 where month = 7;
update rogan.sent set s = 2 where month = 8;
update rogan.sent set s = 3 where month = 9;
update rogan.sent set s = 4 where month = 10;
update rogan.sent set s = 5 where month = 11;
update rogan.sent set s = 6 where month = 12;
update rogan.sent set s = 7 where month = 1;
update rogan.sent set s = 8 where month = 2;
update rogan.sent set s = 9 where month = 3;
update rogan.sent set s = 10 where month = 4;
update rogan.sent set s = 11 where month = 5;
update rogan.sent set s = 12 where month = 6;
alter table rogan.sent add column m text;
update rogan.sent set m = 'July' where month = 7;
update rogan.sent set m = 'August' where month = 8;
update rogan.sent set m = 'September' where month = 9;
update rogan.sent set m = 'October' where month = 10;
update rogan.sent set m = 'November' where month = 11;
update rogan.sent set m = 'December' where month = 12;
update rogan.sent set m = 'January' where month = 1;
update rogan.sent set m = 'February' where month = 2;
update rogan.sent set m = 'March' where month = 3;
update rogan.sent set m = 'April' where month = 4;
update rogan.sent set m = 'May' where month = 5;
update rogan.sent set m = 'June' where month = 6;

select "sent count", shortname as library, m as month from rogan.sent order by shortname, s;

drop table rogan.sent;

create table rogan.dest as
select count(atc.id) as "received count", parent.shortname, extract(month from atc.dest_recv_time) as month
from action.transit_copy atc
left join actor.org_unit aou on aou.id = atc.source
join actor.org_unit parent on parent.id = aou.parent_ou
where
(extract(year from atc.dest_recv_time) = '2014' and extract(month from atc.dest_recv_time) in (7,8,9,10,11,12))
or
(extract(year from atc.dest_recv_time) = '2015' and extract(month from atc.dest_recv_time) in (1,2,3,4,5,6))
group by 2, 3
order by 2, 3
;

alter table rogan.dest add column s int;
update rogan.dest set s = 1 where month = 7;
update rogan.dest set s = 2 where month = 8;
update rogan.dest set s = 3 where month = 9;
update rogan.dest set s = 4 where month = 10;
update rogan.dest set s = 5 where month = 11;
update rogan.dest set s = 6 where month = 12;
update rogan.dest set s = 7 where month = 1;
update rogan.dest set s = 8 where month = 2;
update rogan.dest set s = 9 where month = 3;
update rogan.dest set s = 10 where month = 4;
update rogan.dest set s = 11 where month = 5;
update rogan.dest set s = 12 where month = 6;
alter table rogan.dest add column m text;
update rogan.dest set m = 'July' where month = 7;
update rogan.dest set m = 'August' where month = 8;
update rogan.dest set m = 'September' where month = 9;
update rogan.dest set m = 'October' where month = 10;
update rogan.dest set m = 'November' where month = 11;
update rogan.dest set m = 'December' where month = 12;
update rogan.dest set m = 'January' where month = 1;
update rogan.dest set m = 'February' where month = 2;
update rogan.dest set m = 'March' where month = 3;
update rogan.dest set m = 'April' where month = 4;
update rogan.dest set m = 'May' where month = 5;
update rogan.dest set m = 'June' where month = 6;

select "received count", shortname as library, m as month from rogan.dest order by shortname, s;

drop table rogan.dest;