ICLs as lost items

-- books lost to other libraries by checkout

-- first let's get a list of what books have been checked out and gone to lost

select * from config.copy_status;
-- 3 is lost

select count(id) from asset.copy where status = 3;
-- 125,705

-- now we need to see if these went lost on checkouts to other library systems


select count(circ.id)
from action.circulation circ
join asset.copy ac on ac.id = circ.target_copy
join actor.org_unit copychild on copychild.id = ac.circ_lib
join actor.org_unit copyparent on copyparent.id = copychild.parent_ou
join actor.org_unit circchild on circchild.id = circ.circ_lib
join actor.org_unit circparent on circparent.id = circchild.parent_ou
where copyparent.id != circparent.id and date(circ.xact_start) >= '2013-01-01' and date(circ.xact_start) <= '2013-12-31' and circ.stop_fines = 'LOST'
;

-- 1,230 went to lost while checked out to another library system than who owned the book in 2013

-- so, how many were later found?

select count(circ.id)
from action.circulation circ
join asset.copy ac on ac.id = circ.target_copy
join actor.org_unit copychild on copychild.id = ac.circ_lib
join actor.org_unit copyparent on copyparent.id = copychild.parent_ou
join actor.org_unit circchild on circchild.id = circ.circ_lib
join actor.org_unit circparent on circparent.id = circchild.parent_ou
where copyparent.id != circparent.id and date(circ.xact_start) >= '2013-01-01' and date(circ.xact_start) <= '2013-12-31' and circ.stop_fines = 'LOST'
and ac.status not in (0,6,1,8)
;

-- 881
-- so, 1230 - 881 = 349 so that suggests that 349 items came back into use in some way but there could be wierdness
-- let's look at those explicitly still lost

select count(circ.id)
from action.circulation circ
join asset.copy ac on ac.id = circ.target_copy
join actor.org_unit copychild on copychild.id = ac.circ_lib
join actor.org_unit copyparent on copyparent.id = copychild.parent_ou
join actor.org_unit circchild on circchild.id = circ.circ_lib
join actor.org_unit circparent on circparent.id = circchild.parent_ou
where copyparent.id != circparent.id and date(circ.xact_start) >= '2013-01-01' and date(circ.xact_start) <= '2013-12-31' and circ.stop_fines = 'LOST'
and ac.status = 3
;

-- 863, a difference of 18 only a 2% difference from 881
-- so of those lost of 1,230 881 end up still lost by Feb so 72% stay lost and 28% come back still

-- now let's break it down by system of owning material

select count(circ.id), copyparent.shortname as "copy_owner"
from action.circulation circ
join asset.copy ac on ac.id = circ.target_copy
join actor.org_unit copychild on copychild.id = ac.circ_lib
join actor.org_unit copyparent on copyparent.id = copychild.parent_ou
join actor.org_unit circchild on circchild.id = circ.circ_lib
join actor.org_unit circparent on circparent.id = circchild.parent_ou
where copyparent.id != circparent.id and date(circ.xact_start) >= '2013-01-01' and date(circ.xact_start) <= '2013-12-31' and circ.stop_fines = 'LOST'
and ac.status not in (0,6,1,8)
group by 2
order by 2 asc
;

-- of who lost what


select count(circ.id), circparent.shortname as "library_of_losing_patron"
from action.circulation circ
join asset.copy ac on ac.id = circ.target_copy
join actor.org_unit copychild on copychild.id = ac.circ_lib
join actor.org_unit copyparent on copyparent.id = copychild.parent_ou
join actor.org_unit circchild on circchild.id = circ.circ_lib
join actor.org_unit circparent on circparent.id = circchild.parent_ou
where copyparent.id != circparent.id and date(circ.xact_start) >= '2013-01-01' and date(circ.xact_start) <= '2013-12-31' and circ.stop_fines = 'LOST'
and ac.status not in (0,6,1,8)
group by 2
order by 2 asc
;

-- both?

select count(circ.id), copyparent.shortname as "copy_owner", circparent.shortname as "library_of_losing_patron"
from action.circulation circ
join asset.copy ac on ac.id = circ.target_copy
join actor.org_unit copychild on copychild.id = ac.circ_lib
join actor.org_unit copyparent on copyparent.id = copychild.parent_ou
join actor.org_unit circchild on circchild.id = circ.circ_lib
join actor.org_unit circparent on circparent.id = circchild.parent_ou
where copyparent.id != circparent.id and date(circ.xact_start) >= '2013-01-01' and date(circ.xact_start) <= '2013-12-31' and circ.stop_fines = 'LOST'
and ac.status not in (0,6,1,8)
group by 2, 3
order by 2, 3 ASC
;

-- now, let's see if I can get SQL to do the rest of the work instead of me doing it manaully
-- I could do this next step as as a subquery but I'm lazy and rather than waiting for the big search each time incase I do a lot of tweaking
-- I'm going to make a temp table of it which will make things super fast


create table rogan.icls_lost as
select count(circ.id) as "lost_count", copyparent.shortname as "owning_lib", circparent.shortname as "circ_lib"
from action.circulation circ
join asset.copy ac on ac.id = circ.target_copy
join actor.org_unit copychild on copychild.id = ac.circ_lib
join actor.org_unit copyparent on copyparent.id = copychild.parent_ou
join actor.org_unit circchild on circchild.id = circ.circ_lib
join actor.org_unit circparent on circparent.id = circchild.parent_ou
where copyparent.id != circparent.id and date(circ.xact_start) >= '2013-01-01' and date(circ.xact_start) <= '2013-12-31' and circ.stop_fines = 'LOST'
and ac.status not in (0,6,1,8)
group by 2, 3
order by 2, 3 ASC
;

-- time for the seldom (for me anywya) (in evergreen) used cross join

-- let's match them up now

select lost.lost_count as "a_lost_count", lost.owning_lib as "a_owning_lib", lost.circ_lib as "a_circ_lib",
owning.lost_count as "b_lost_count", owning.owning_lib as "b.owning_lib", owning.circ_lib as "b.circ_lib"
from rogan.icls_lost lost
cross join rogan.icls_lost owning
where owning.owning_lib = lost.circ_lib and owning.circ_lib = lost.owning_lib
;

-- now let's do math

select (lost.lost_count - owning.lost_count) as "sum_count", lost.owning_lib as "a_owning_lib", lost.circ_lib as "a_circ_lib",
owning.owning_lib as "b.owning_lib", owning.circ_lib as "b.circ_lib"
from rogan.icls_lost lost
cross join rogan.icls_lost owning
where owning.owning_lib = lost.circ_lib and owning.circ_lib = lost.owning_lib
;

-- now notice that the values will a variety of real numbers, positves, negatives and zeros
-- and a value like 9 will have an inverse pair with a value of -9, let's get rid of these and the zeros.  The zeros are literally zeroed out
-- so we only care about negative or postive values and both describe the same relationship from different vectors
-- so we can arbitrarily pick either


select (lost.lost_count - owning.lost_count) as "sum_count", lost.owning_lib as "a_owning_lib", lost.circ_lib as "a_circ_lib",
owning.owning_lib as "b.owning_lib", owning.circ_lib as "b.circ_lib"
from rogan.icls_lost lost
cross join rogan.icls_lost owning
where owning.owning_lib = lost.circ_lib and owning.circ_lib = lost.owning_lib
group by 1, 2, 3, 4, 5
having (lost.lost_count - owning.lost_count) > 0
;

-- let's do more cleanup

select (lost.lost_count - owning.lost_count) as "sum_count", lost.owning_lib as "owning_lib", lost.circ_lib as "library_of_losing_patron"
from rogan.icls_lost lost
cross join rogan.icls_lost owning
where owning.owning_lib = lost.circ_lib and owning.circ_lib = lost.owning_lib
group by 1, 2, 3
having (lost.lost_count - owning.lost_count) > 0
order by 2, 3 asc
;


select * from rogan.icls_lost;