Annual Report - Section F old

/*

Section F LIBRARY COLLECTIONS

            Books/Serials/Volumes - Physical print format
            F1 Number added__________________________
            F2 Number weeded__________________________
            F3 TOTAL__________________________
            Current Print Serial Subscriptions (physical format, newspaper and periodicals)
            F4 Number added__________________________
            F5 Number weeded__________________________
            F6 TOTAL__________________________

            Audio materials (physical units containing music, spoken word, etc. on CD, cassette tape, etc.)
            F7 Number of physical units added__________________________
            F8 Number of physical units weeded__________________________
            F9 TOTAL__________________________

            Video materials (physical units containing movies, TV shows, etc. on film, DVD, etc.)
            F10 Number of physical units added__________________________
            F11 Number of physical units weeded__________________________
            F12 TOTAL physical units held__________________________

*/


/*
total language materials (aka books) in collection at end of fiscal
there isn't an "other" category for materials so everything else is grouped into physical print
also adds in an estimate based on your non-cataloged circulation of those materials
------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------
*/
SELECT SUM(a.copy_id) AS "Books Total - F3", a.org2_shortname
FROM (
        SELECT coalesce(COUNT(DISTINCT COPY.id),0) 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) <= '2015-06-30'
        AND meta.tag = 'LDR' AND SUBSTRING(meta.VALUE FROM 7 FOR 1) IN ('a','c','d','e','f','k','m','o','p','r','t')
        AND SUBSTRING(meta.VALUE FROM 8 FOR 1) != 's'
        GROUP BY 2
UNION ALL
        SELECT coalesce(COUNT(DISTINCT COPY.id),0) 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) <= '2015-06-30'
        AND date(COPY.edit_date) >= '2014-07-01' AND date(COPY.edit_date) <= '2015-06-30'
        AND meta.tag = 'LDR' AND SUBSTRING(meta.VALUE FROM 7 FOR 1) IN ('a','c','d','e','f','k','m','o','p','r','t')
        AND SUBSTRING(meta.VALUE FROM 8 FOR 1) != 's'
        GROUP BY 2
UNION ALL
    SELECT coalesce(count(circ.id),0) AS copy_id, org2.shortname AS org2_shortname
        FROM action.non_cataloged_circulation circ
        left 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) >= '2014-07-01' AND date(circ.circ_time) <= '2015-06-30'
        GROUP BY 2
) a
GROUP BY 2
ORDER BY 2
;


/*
serials looking at position 8, requires it be a print material like those counted above
------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------
*/
SELECT SUM(a.copy_id) AS "Serials - F6", a.org2_shortname
FROM (
        SELECT coalesce(COUNT(distinct COPY.id),0) 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) <= '2015-06-30'
        AND meta.tag = 'LDR' AND SUBSTRING(meta.VALUE FROM 8 FOR 1) = 's'
        AND SUBSTRING(meta.VALUE FROM 7 FOR 1) IN ('a','c','d','e','f','k','m','o','p','r','t')

        GROUP BY 2
UNION ALL
        SELECT coalesce(COUNT(distinct COPY.id),0) 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) <= '2015-06-30'
        AND date(COPY.edit_date) >= '2014-07-01' AND date(COPY.edit_date) <= '2015-06-30'
        AND meta.tag = 'LDR' AND SUBSTRING(meta.VALUE FROM 8 FOR 1) = 's'
        AND SUBSTRING(meta.VALUE FROM 7 FOR 1) IN ('a','c','d','e','f','k','m','o','p','r','t')
        GROUP BY 2
) a
GROUP BY 2
ORDER BY 2
;


/*
total j = music and i = audio books  in collection at end of fiscal
------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------
*/
SELECT SUM(a.copy_id) AS "Audio - F9", a.org2_shortname
FROM (
        SELECT coalesce(COUNT(distinct COPY.id),0) 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) <= '2015-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 coalesce(COUNT(distinct COPY.id),0) 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) <= '2015-06-30'
        AND date(COPY.edit_date) >= '2014-07-01' AND date(COPY.edit_date) <= '2015-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 videos in collection at end of fiscal
first search grabs undeleted items created on or before the cutoff date
second search grabs items deleted during that year
answers F12
------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------
*/
SELECT SUM(a.copy_id) AS "Total Videos - F12", a.org2_shortname
FROM (
        SELECT coalesce(COUNT(DISTINCT COPY.id),0) 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) <= '2015-06-30'
        AND meta.tag = 'LDR' AND SUBSTRING(meta.VALUE FROM 7 FOR 1) = 'g'
        GROUP BY 2
UNION ALL
        SELECT coalesce(COUNT(DISTINCT COPY.id),0) 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) <= '2015-06-30'
        AND date(COPY.edit_date) >= '2014-07-01' AND date(COPY.edit_date) <= '2015-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
;


/*
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 v.shortname AS "Library",
coalesce(SUM(t.books),0) AS "Books Added - F1", coalesce(SUM(e.serials),0) AS "Serials Added - F4",
coalesce(SUM(s.sound),0) AS "Audios Added - F7", coalesce(SUM(v.videos),0) AS "Videos Added - F10"
FROM (
        SELECT coalesce(COUNT(DISTINCT COPY.id),0) 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) >= '2014-07-01' AND date(COPY.create_date) <= '2015-06-30'
        AND meta.tag = 'LDR'
        AND SUBSTRING(meta.VALUE FROM 7 FOR 1) = 'g'
        GROUP BY 2
) v
left JOIN (
        SELECT coalesce(COUNT(DISTINCT COPY.id),0) 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) >= '2014-07-01' AND date(COPY.create_date) <= '2015-06-30'
        AND meta.tag = 'LDR'
        AND SUBSTRING(meta.VALUE FROM 7 FOR 1) IN ('a','c','d','e','f','k','m','o','p','r','t')
        AND SUBSTRING(meta.VALUE FROM 8 FOR 1) != 's'
        GROUP BY 2
) t ON t.shortname = v.shortname
left JOIN (
        SELECT coalesce(COUNT(DISTINCT COPY.id),0) 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) >= '2014-07-01' AND date(COPY.create_date) <= '2015-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
left JOIN (
        SELECT coalesce(COUNT(DISTINCT COPY.id),0) AS serials, 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) >= '2014-07-01' AND date(COPY.create_date) <= '2015-06-30'
        AND meta.tag = 'LDR'
        AND SUBSTRING(meta.VALUE FROM 7 FOR 1) IN ('a','c','d','e','f','k','m','o','p','r','t')
        AND SUBSTRING(meta.VALUE FROM 8 FOR 1) = 's'
        GROUP BY 2
) e ON e.shortname = v.shortname
GROUP BY 1
ORDER BY 1
;


/*
materials weeded 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 v.shortname AS "Library",
coalesce(SUM(t.books),0) AS "Books Weeded - F2", coalesce(SUM(e.serials),0) AS "Serials Weeded - F5 ",
coalesce(SUM(s.sound),0) AS "Audio Weeded - F8", coalesce(SUM(v.videos),0) AS "Videos Weeded - F11"
FROM (
        SELECT coalesce(COUNT(DISTINCT COPY.id),0) 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) >= '2014-07-01' AND date(COPY.edit_date) <= '2015-06-30'
        AND meta.tag = 'LDR'
        AND SUBSTRING(meta.VALUE FROM 7 FOR 1) = 'g'
        GROUP BY 2
) v
left JOIN (
        SELECT coalesce(COUNT(DISTINCT COPY.id),0) 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) >= '2014-07-01' AND date(COPY.edit_date) <= '2015-06-30'
        AND meta.tag = 'LDR'
        AND SUBSTRING(meta.VALUE FROM 7 FOR 1) IN ('a','c','d','e','f','k','m','o','p','r','t')
        AND SUBSTRING(meta.VALUE FROM 8 FOR 1) != 's'
        GROUP BY 2
) t ON t.shortname = v.shortname
left JOIN (
        SELECT coalesce(COUNT(DISTINCT COPY.id),0) 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) >= '2014-07-01' AND date(COPY.edit_date) <= '2015-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
left JOIN (
        SELECT coalesce(COUNT(DISTINCT COPY.id),0) AS serials, 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) >= '2014-07-01' AND date(COPY.edit_date) <= '2015-06-30'
        AND meta.tag = 'LDR'
        AND SUBSTRING(meta.VALUE FROM 7 FOR 1) IN ('a','c','d','e','f','k','m','o','p','r','t')
        AND SUBSTRING(meta.VALUE FROM 8 FOR 1) = 's'
        GROUP BY 2
) e ON e.shortname = v.shortname
GROUP BY 1
ORDER BY 1
;