Annual State Statistics - old

-- SCLENDS is part of a consortium and this assumes that the library recorded in action.circulation.circ_lib
-- should get the credit for the circulation


/*
total videos in collection at end of fiscal - deleted or not
------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------
*/
select sum(a.copy_id) as "Videos", a.org2_shortname
from (
    select count(copy.id) as copy_id, org2.shortname as org2_shortname
    from asset.copy copy
    join actor.org_unit org on org.id = copy.circ_lib
    join actor.org_unit org2 on org2.id = org.parent_ou
    join asset.call_number acn on acn.id = copy.call_number
    left outer join metabib.real_full_rec meta on meta.record = acn.record
    where copy.deleted = FALSE and date(copy.create_date) <= '2013-06-30'
    and meta.tag = 'LDR' and substring(meta.value from 7 for 1) = 'g'
    group by 2
UNION ALL
    select count(copy.id) as copy_id, org2.shortname as org2_shortname
    from asset.copy copy
    join actor.org_unit org on org.id = copy.circ_lib
    join actor.org_unit org2 on org2.id = org.parent_ou
    join asset.call_number acn on acn.id = copy.call_number
    left outer join metabib.real_full_rec meta on meta.record = acn.record
    where copy.deleted = TRUE and date(copy.create_date) <= '2013-06-30'
    and date(copy.edit_date) >= '2012-07-01' and date(copy.edit_date) <= '2013-06-30'
    and meta.tag = 'LDR' and substring(meta.value from 7 for 1) = 'g'
    group by 2
) a
group by 2
order by 2
;


/*
total language mateerials (aka books) in collection at end of fiscal - deleted or not
------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------
*/


select sum(a.copy_id) as "Books", a.org2_shortname
from (
    select count(copy.id) as copy_id, org2.shortname as org2_shortname
    from asset.copy copy
    join actor.org_unit org on org.id = copy.circ_lib
    join actor.org_unit org2 on org2.id = org.parent_ou
    join asset.call_number acn on acn.id = copy.call_number
    left outer join metabib.real_full_rec meta on meta.record = acn.record
    where copy.deleted = FALSE and date(copy.create_date) <= '2013-06-30'
    and meta.tag = 'LDR' and substring(meta.value from 7 for 1) = 'a'
    group by 2
UNION ALL
    select count(copy.id) as copy_id, org2.shortname as org2_shortname
    from asset.copy copy
    join actor.org_unit org on org.id = copy.circ_lib
    join actor.org_unit org2 on org2.id = org.parent_ou
    join asset.call_number acn on acn.id = copy.call_number
    left outer join metabib.real_full_rec meta on meta.record = acn.record
    where copy.deleted = TRUE and date(copy.create_date) <= '2013-06-30'
    and date(copy.edit_date) >= '2012-07-01' and date(copy.edit_date) <= '2013-06-30'
    and meta.tag = 'LDR' and substring(meta.value from 7 for 1) = 'a'
    group by 2
) a
group by 2
order by 2
;


/*
total j = music and i = audio books  in collection at end of fiscal - deleted or not
------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------
*/
select sum(a.copy_id) as "Audio", a.org2_shortname
from (
    select count(copy.id) as copy_id, org2.shortname as org2_shortname
    from asset.copy copy
    join actor.org_unit org on org.id = copy.circ_lib
    join actor.org_unit org2 on org2.id = org.parent_ou
    join asset.call_number acn on acn.id = copy.call_number
    left outer join metabib.real_full_rec meta on meta.record = acn.record
    where copy.deleted = FALSE and date(copy.create_date) <= '2013-06-30'
    and meta.tag = 'LDR' and (substring(meta.value from 7 for 1) = 'j' or substring(meta.value from 7 for 1) = 'i')
    group by 2
UNION ALL
    select count(copy.id) as copy_id, org2.shortname as org2_shortname
    from asset.copy copy
    join actor.org_unit org on org.id = copy.circ_lib
    join actor.org_unit org2 on org2.id = org.parent_ou
    join asset.call_number acn on acn.id = copy.call_number
    left outer join metabib.real_full_rec meta on meta.record = acn.record
    where copy.deleted = TRUE and date(copy.create_date) <= '2013-06-30'
    and date(copy.edit_date) >= '2012-07-01' and date(copy.edit_date) <= '2013-06-30'
    and meta.tag = 'LDR' and (substring(meta.value from 7 for 1) = 'j' or substring(meta.value from 7 for 1) = 'i')
    group by 2
) a
group by 2
order by 2
;


/*
total mateirals in collection at end of fiscal - deleted or not, broken down by marc record type
------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------
*/

select sum(a.copy_id) as "Other", a.org2_shortname
from (
    select count(copy.id) as copy_id, org2.shortname as org2_shortname
    from asset.copy copy
    join actor.org_unit org on org.id = copy.circ_lib
    join actor.org_unit org2 on org2.id = org.parent_ou
    join asset.call_number acn on acn.id = copy.call_number
    left outer join metabib.real_full_rec meta on meta.record = acn.record
    where copy.deleted = FALSE and date(copy.create_date) <= '2013-06-30'
    and meta.tag = 'LDR' and substring(meta.value from 7 for 1) in ('c','d','e','f','k','m','o','p','r','t')
    group by 2
UNION ALL
    select count(copy.id) as copy_id, org2.shortname as org2_shortname
    from asset.copy copy
    join actor.org_unit org on org.id = copy.circ_lib
    join actor.org_unit org2 on org2.id = org.parent_ou
    join asset.call_number acn on acn.id = copy.call_number
    left outer join metabib.real_full_rec meta on meta.record = acn.record
    where copy.deleted = TRUE and date(copy.create_date) <= '2013-06-30'
    and date(copy.edit_date) >= '2012-07-01' and date(copy.edit_date) <= '2013-06-30'
    and meta.tag = 'LDR' and substring(meta.value from 7 for 1) in ('c','d','e','f','k','m','o','p','r','t')
    group by 2
) a
group by 2
order by 2
;

/*
materials added within the fiscal year
-------------------------------------- looks for a = language material, j = music, g = projected medium
-------------------------------------- i = audio books are bundeled with j music as per SC requirements
------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------
*/

select sum(t.books) as "Books", sum(s.sound) as "Audios", sum(v.videos) as "Videos",
sum(o.other) as "Others", v.shortname as "Library"
from (
    select count(copy.id) as videos, org2.shortname as shortname
    from asset.copy copy
    join actor.org_unit org on org.id = copy.circ_lib
    join actor.org_unit org2 on org2.id = org.parent_ou
    join asset.call_number call on call.id = copy.call_number
    left outer join metabib.real_full_rec meta on meta.record = call.record
    where date(copy.create_date) >= '2012-07-01' and date(copy.create_date) <= '2013-06-30'
    and meta.tag = 'LDR'
    and substring(meta.value from 7 for 1) = 'g'
    group by 2
) v
join (
    select count(copy.id) as books, org2.shortname as shortname
    from asset.copy copy
    join actor.org_unit org on org.id = copy.circ_lib
    join actor.org_unit org2 on org2.id = org.parent_ou
    join asset.call_number call on call.id = copy.call_number
    left outer join metabib.real_full_rec meta on meta.record = call.record
    where date(copy.create_date) >= '2012-07-01' and date(copy.create_date) <= '2013-06-30'
    and meta.tag = 'LDR'
    and substring(meta.value from 7 for 1) = 'a'
    group by 2
) t on t.shortname = v.shortname
join (
    select count(copy.id) as sound, org2.shortname as shortname
    from asset.copy copy
    join actor.org_unit org on org.id = copy.circ_lib
    join actor.org_unit org2 on org2.id = org.parent_ou
    join asset.call_number call on call.id = copy.call_number
    left outer join metabib.real_full_rec meta on meta.record = call.record
    where date(copy.create_date) >= '2012-07-01' and date(copy.create_date) <= '2013-06-30'
    and meta.tag = 'LDR'
    and substring(meta.value from 7 for 1) in ('i','j')
    group by 2
) s on s.shortname = v.shortname
join (
    select count(copy.id) as other, org2.shortname as shortname
    from asset.copy copy
    join actor.org_unit org on org.id = copy.circ_lib
    join actor.org_unit org2 on org2.id = org.parent_ou
    join asset.call_number call on call.id = copy.call_number
    left outer join metabib.real_full_rec meta on meta.record = call.record
    where date(copy.create_date) >= '2012-07-01' and date(copy.create_date) <= '2013-06-30'
    and meta.tag = 'LDR'
    and substring(meta.value from 7 for 1) in ('c','d','e','f','k','m','o','p','r','t')
    group by 2
) o on o.shortname = v.shortname
group by 5
order by 5
;

/*
materials added by end of fiscal - deleted or not
-------------------------------------- looks for a = language material, j = music, g = projected medium
-------------------------------------- i = audio books are bundeled with j music as per SC requirements
------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------
*/

select sum(t.books) as "Books Weeded", sum(s.sound) as "Audio Weeded", sum(v.videos) as "Videos Weeded",
sum(o.other) as "Other Weeded", v.shortname as "Library"
from (
    select count(copy.id) as videos, org2.shortname as shortname
    from asset.copy copy
    join actor.org_unit org on org.id = copy.circ_lib
    join actor.org_unit org2 on org2.id = org.parent_ou
    join asset.call_number call on call.id = copy.call_number
    left outer join metabib.real_full_rec meta on meta.record = call.record
    where copy.deleted = TRUE and date(copy.edit_date) >= '2012-07-01' and date(copy.edit_date) <= '2013-06-30'
    and meta.tag = 'LDR'
    and substring(meta.value from 7 for 1) = 'g'
    group by 2
) v
join (
    select count(copy.id) as books, org2.shortname as shortname
    from asset.copy copy
    join actor.org_unit org on org.id = copy.circ_lib
    join actor.org_unit org2 on org2.id = org.parent_ou
    join asset.call_number call on call.id = copy.call_number
    left outer join metabib.real_full_rec meta on meta.record = call.record
    where copy.deleted = TRUE and date(copy.edit_date) >= '2012-07-01' and date(copy.edit_date) <= '2013-06-30'
    and meta.tag = 'LDR'
    and substring(meta.value from 7 for 1) = 'a'
    group by 2
) t on t.shortname = v.shortname
join (
    select count(copy.id) as sound, org2.shortname as shortname
    from asset.copy copy
    join actor.org_unit org on org.id = copy.circ_lib
    join actor.org_unit org2 on org2.id = org.parent_ou
    join asset.call_number call on call.id = copy.call_number
    left outer join metabib.real_full_rec meta on meta.record = call.record
    where copy.deleted = TRUE and date(copy.edit_date) >= '2012-07-01' and date(copy.edit_date) <= '2013-06-30'
    and meta.tag = 'LDR'
    and substring(meta.value from 7 for 1) in ('i','j')
    group by 2
) s on s.shortname = v.shortname
join (
    select count(copy.id) as other, org2.shortname as shortname
    from asset.copy copy
    join actor.org_unit org on org.id = copy.circ_lib
    join actor.org_unit org2 on org2.id = org.parent_ou
    join asset.call_number call on call.id = copy.call_number
    left outer join metabib.real_full_rec meta on meta.record = call.record
    where copy.deleted = TRUE and date(copy.edit_date) >= '2012-07-01' and date(copy.edit_date) <= '2013-06-30'
    and meta.tag = 'LDR'
    and substring(meta.value from 7 for 1) in ('c','d','e','f','k','m','o','p','r','t')
    group by 2
) o on o.shortname = v.shortname
group by 5
order by 5
;

/*
counts patron accounts under the age of 18 by the end of the fiscal year where the age is blank due to the dob
being null because we used to allow that.  we look to see if the account is labeled as teen, juvenile, youth or child.
(254,259,145,146,147,148,165,166,167,168,171,172,173,174,207,215,226,193,194,195,189,190,191,192,270,271,240,241)
I check for dob first becuase it could be an adult in a child account but I assume if the dob is null that it is on the
exception list above and a child, I look to see if it's marked deleted and if it was marked past the cut off
and if it was created in the last three years

NOTES: patron profile consolidation will affect this as will the usr activity tracking
*/

-- all accounts for checking against

select count(usr.id) as "Patrons", org2.shortname
from actor.usr usr
join actor.org_unit org on org.id = usr.home_ou
join actor.org_unit org2 on org2.id = org.parent_ou
where
usr.deleted = FALSE
and
( date(usr.expire_date) >= '2010-07-01' or date(usr.create_date) >= '2010-07-01' )
group by 2
order by 2
;
/* juvenile accounts, technically it would be better to update via a last update and get those fixed than usr.create but there are so few, it doesn't matter */

select count(usr.id) as "Juveniles", org2.shortname
from actor.usr usr
join actor.org_unit org on org.id = usr.home_ou
join actor.org_unit org2 on org2.id = org.parent_ou
where
(
    date(usr.create_date) >= '2010-07-01'
    or
    (
        usr.deleted = FALSE
        and
        date(usr.expire_date) >= '2010-07-01'
    )
)
and
    (
        date(usr.dob) >= date('1995-07-01')
        or
        (dob is null and usr.profile in (254,259,145,146,147,148,165,166,167,168,171,172,173,174,207,215,226,193,194,195,189,190,191,192,270,271,240,241))
    )
group by 2
order by 2
;


-- adult patrons, basically inverse of above

select count(usr.id) as "Adults", org2.shortname
from actor.usr usr
join actor.org_unit org on org.id = usr.home_ou
join actor.org_unit org2 on org2.id = org.parent_ou
where
(
    date(usr.create_date) >= '2010-07-01'
    or
    (
        usr.deleted = FALSE
        and
        date(usr.expire_date) >= '2010-07-01'
    )
)
and
    (
        date(usr.dob) <= date('1995-07-01')
        or
        (dob is null and usr.profile not in (254,259,145,146,147,148,165,166,167,168,171,172,173,174,207,215,226,193,194,195,189,190,191,192,270,271,240,241))
    )
group by 2
order by 2
;


-------------------------------------- circ by org units
------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------


select circ_cat.shortname, circ_cat.circs + circ_non.circs
from
    (   select count(circ.id) as circs, org2.shortname
        from 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 date(circ.xact_start) >= '2012-07-01' and date(circ.xact_start) <= '2013-06-30'
        group by 2
    ) circ_cat
join (  select count(circ.id) as circs, org2.shortname
        from action.non_cataloged_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 date(circ.circ_time) >= '2012-07-01' and date(circ.circ_time) <= '2013-06-30'
        group by 2
    ) circ_non on circ_non.shortname = circ_cat.shortname
order by 1 asc;



-------------------------------------- circ by material format from leader
-------------------------------------- looks for a = language material, j = music, g = projected medium
-------------------------------------- i = audio books, do they go into an 'audio' or a 'book' category?
-------------------------------------- and other
-------------------------------------- for non-cat circs they're all added to language materials
------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------
SELECT SUM(t.text_circs) AS "Book Circs", SUM(s.sound_circs)+SUM(ab.audiobook_circs) AS "Sound Circs", SUM(v.video_circs) AS "Video Circs",
SUM(o.other_circs) AS "Other Circs", v.shortname AS "Library"
FROM (
        SELECT COUNT(circ.id) AS video_circs, org2.shortname AS shortname
        FROM 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
        JOIN asset.COPY COPY ON COPY.id = circ.target_copy
        JOIN asset.call_number call ON call.id = COPY.call_number
        LEFT OUTER JOIN metabib.real_full_rec meta ON meta.record = call.record
        WHERE date(circ.xact_start) >= '2012-07-01' AND date(circ.xact_start) <= '2013-06-30'
        AND meta.tag = 'LDR'
        AND SUBSTRING(meta.VALUE FROM 7 FOR 1) = 'g'
        GROUP BY 2
) v
JOIN (
        SELECT circ_cat.shortname as shortname, (circ_cat.circs + circ_non.circs) as text_circs
        FROM
        (
                SELECT COUNT(circ.id) AS circs, org2.shortname AS shortname
                FROM 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
                JOIN asset.COPY COPY ON COPY.id = circ.target_copy
                JOIN asset.call_number call ON call.id = COPY.call_number
                LEFT JOIN metabib.real_full_rec meta ON meta.record = call.record
                WHERE date(circ.xact_start) >= '2012-07-01' AND date(circ.xact_start) <= '2013-06-30'
                AND meta.tag = 'LDR'
                AND SUBSTRING(meta.VALUE FROM 7 FOR 1) = 'a'
                GROUP BY 2
        ) circ_cat
        JOIN
        (       SELECT COUNT(circ.id) AS circs, org2.shortname
                FROM ACTION.non_cataloged_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 date(circ.circ_time) >= '2012-07-01' AND date(circ.circ_time) <= '2013-06-30'
                GROUP BY 2
        ) circ_non ON circ_non.shortname = circ_cat.shortname
) t ON t.shortname = v.shortname
JOIN (
        SELECT COUNT(circ.id) AS sound_circs, org2.shortname AS shortname
        FROM 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
        JOIN asset.COPY COPY ON COPY.id = circ.target_copy
        JOIN asset.call_number call ON call.id = COPY.call_number
        LEFT JOIN metabib.real_full_rec meta ON meta.record = call.record
        WHERE date(circ.xact_start) >= '2012-07-01' AND date(circ.xact_start) <= '2013-06-30'
        AND meta.tag = 'LDR'
        AND SUBSTRING(meta.VALUE FROM 7 FOR 1) = 'j'
        GROUP BY 2
) s ON s.shortname = v.shortname
JOIN (
        SELECT COUNT(circ.id) AS audiobook_circs, org2.shortname AS shortname
        FROM 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
        JOIN asset.COPY COPY ON COPY.id = circ.target_copy
        JOIN asset.call_number call ON call.id = COPY.call_number
        LEFT JOIN metabib.real_full_rec meta ON meta.record = call.record
        WHERE date(circ.xact_start) >= '2012-07-01' AND date(circ.xact_start) <= '2013-06-30'
        AND meta.tag = 'LDR'
        AND SUBSTRING(meta.VALUE FROM 7 FOR 1) = 'i'
        GROUP BY 2
) ab ON ab.shortname = v.shortname
JOIN (
        SELECT COUNT(circ.id) AS other_circs, org2.shortname AS shortname
        FROM 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
        JOIN asset.COPY COPY ON COPY.id = circ.target_copy
        JOIN asset.call_number call ON call.id = COPY.call_number
        LEFT JOIN metabib.real_full_rec meta ON meta.record = call.record
        WHERE date(circ.xact_start) >= '2012-07-01' AND date(circ.xact_start) <= '2013-06-30'
        AND meta.tag = 'LDR'
        AND SUBSTRING(meta.VALUE FROM 7 FOR 1) IN ('c','d','e','f','k','m','o','p','r','t')
        GROUP BY 2
) o ON o.shortname = v.shortname
GROUP BY 5
ORDER BY 5
;


-------------------------------------- circ by audience drawn from copy location
-------------------------------------- adults get the non cat circs
------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------


select sum(a.adult_circs) as "Adult Circs", sum(y.young_circs) as "Young Adult Circs", sum(j.juv_circs) as "Juvenile Circs",
sum(o.other_circs) as "Other Circs", a.org2_shortname as "Library"
from
(
        SELECT circ_cat.shortname as org2_shortname, (circ_cat.circs + circ_non.circs) as adult_circs
        FROM
        (
            select count(circ.id) as circs, org2.shortname as shortname
            from 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
            join asset.copy_location loc on loc.id = circ.copy_location
            where date(circ.xact_start) >= '2012-07-01' and date(circ.xact_start) <= '2013-06-30'
            and loc.name like 'Adu%'
            group by 2
         ) circ_cat
        JOIN
        (   SELECT COUNT(circ.id) AS circs, org2.shortname
            FROM ACTION.non_cataloged_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 date(circ.circ_time) >= '2012-07-01' AND date(circ.circ_time) <= '2013-06-30'
            GROUP BY 2
        ) circ_non ON circ_non.shortname = circ_cat.shortname
) a
join (
    select count(circ.id) as young_circs, org2.shortname as org2_shortname
    from action.circulation circ
    join actor.org_unit org on org.id = circ.circ_lib
    left join actor.org_unit org2 on org2.id = org.parent_ou
    join asset.copy_location loc on loc.id = circ.copy_location
    where date(circ.xact_start) >= '2012-07-01' and date(circ.xact_start) <= '2013-06-30'
    and loc.name like 'You%'
    group by 2
) y on y.org2_shortname = a.org2_shortname
join (
    select count(circ.id) as juv_circs, org2.shortname as org2_shortname
    from 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
    join asset.copy_location loc on loc.id = circ.copy_location
    where date(circ.xact_start) >= '2012-07-01' and date(circ.xact_start) <= '2013-06-30'
    and loc.name like 'Juv%'
    group by 2
) j on j.org2_shortname = a.org2_shortname
join (
    select count(circ.id) as other_circs, org2.shortname as org2_shortname
    from 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
    join asset.copy_location loc on loc.id = circ.copy_location
    where date(circ.xact_start) >= '2012-07-01' and date(circ.xact_start) <= '2013-06-30'
    and loc.name not like 'Adu%' and loc.name not like 'Juv%' and loc.name not like 'You%'
    group by 2
) o on o.org2_shortname = a.org2_shortname
group by 5
order by 5
;




-------------------------------------- total items in collection as of end of fiscal year, deleted and not
------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------

select sum(a.copy_id), a.org2_shortname
from (
    select count(copy.id) as copy_id, org2.shortname as org2_shortname
    from asset.copy copy
    join actor.org_unit org on org.id = copy.circ_lib
    join actor.org_unit org2 on org2.id = org.parent_ou
    where copy.deleted = FALSE and date(copy.create_date) <= '2013-06-30'
    group by 2
UNION ALL
    select count(copy.id) as copy_id, org2.shortname as org2_shortname
    from asset.copy copy
    join actor.org_unit org on org.id = copy.circ_lib
    join actor.org_unit org2 on org2.id = org.parent_ou
    where copy.deleted = TRUE and date(copy.create_date) <= '2013-06-30'
    and date(copy.edit_date) >= '2012-07-01' and date(copy.edit_date) <= '2013-06-30'
    group by 2
) a
group by 2
order by 2
;