Annual State Statistics - Section F Collections

/*

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
------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------
*/


DROP TABLE IF EXISTS rogan.asset_copy;

/* create a table and map per circulation */

CREATE TABLE rogan.asset_copy AS
SELECT id, circ_lib, LOCATION, circ_modifier, deleted, create_date, edit_date FROM asset.COPY;

ALTER TABLE rogan.asset_copy ADD COLUMN book BOOLEAN DEFAULT TRUE;
ALTER TABLE rogan.asset_copy ADD COLUMN serial BOOLEAN DEFAULT FALSE;
ALTER TABLE rogan.asset_copy ADD COLUMN video BOOLEAN DEFAULT FALSE;
ALTER TABLE rogan.asset_copy ADD COLUMN audio BOOLEAN DEFAULT FALSE;

UPDATE rogan.asset_copy SET book = FALSE WHERE circ_modifier IN
        ('ELECTRONIC EQUIPMENT','EREADER','EQUIPMENT','INTERNET','THEME KIT','GAME');

UPDATE rogan.asset_copy SET serial = TRUE WHERE circ_modifier = 'MAGAZINE';

UPDATE rogan.asset_copy SET video = TRUE WHERE circ_modifier IN ('VIDEO','LEASE DVD');

UPDATE rogan.asset_copy SET audio = TRUE WHERE circ_modifier IN ('AUDIO','PLAYAWAY','AUDIO BOOK');

ALTER TABLE rogan.asset_copy ADD COLUMN location_name TEXT;

UPDATE rogan.asset_copy SET location_name = loc.NAME
FROM (SELECT ac.id AS "ac_id", acl.NAME FROM rogan.asset_copy ac JOIN asset.copy_location acl ON acl.id = ac.LOCATION) loc
WHERE loc.ac_id = id ;

UPDATE rogan.asset_copy SET video = TRUE WHERE circ_modifier IN ('21 DAY LEASE','14 DAY LEASE','7 DAY LEASE','28 DAY','14 DAY','21 DAY','7 DAY','RENTAL')
AND (location_name ILIKE '%video%' OR location_name ILIKE '%dvd%');

UPDATE rogan.asset_copy SET audio = TRUE WHERE circ_modifier IN ('21 DAY LEASE','14 DAY LEASE','7 DAY LEASE','28 DAY','14 DAY','21 DAY','7 DAY','RENTAL')
AND (location_name ILIKE '%audio%' OR location_name ILIKE '%books on%' OR location_name ILIKE '%music%');

UPDATE rogan.asset_copy SET book = FALSE WHERE circ_modifier IN ('21 DAY LEASE','14 DAY LEASE','7 DAY LEASE','28 DAY','14 DAY','21 DAY','7 DAY','RENTAL')
AND (location_name ILIKE '%equipment%' OR location_name ILIKE '%adult 360%' OR location_name ILIKE '%adult ps2%');

UPDATE rogan.asset_copy SET book = FALSE WHERE serial = TRUE;
UPDATE rogan.asset_copy SET book = FALSE WHERE video = TRUE;
UPDATE rogan.asset_copy SET book = FALSE WHERE audio = TRUE;


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 rogan.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) <= '2015-06-30'
        AND COPY.book = TRUE
        GROUP BY 2
UNION ALL
        SELECT COALESCE(COUNT(DISTINCT COPY.id),0) AS copy_id, org2.shortname AS org2_shortname
        FROM rogan.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) <= '2015-06-30'
        and date(copy.edit_date) between '2014-07-01' and '2015-06-30'
        AND COPY.book = TRUE
        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) between '2014-07-01' AND '2015-06-30'
        GROUP BY 2
) a
GROUP BY 2
ORDER BY 2
;


/*
------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------
*/
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 rogan.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) <= '2015-06-30'
        AND COPY.serial = TRUE
        GROUP BY 2
UNION ALL
        SELECT COALESCE(COUNT(DISTINCT COPY.id),0) AS copy_id, org2.shortname AS org2_shortname
        FROM rogan.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) <= '2015-06-30'
        AND COPY.serial = TRUE
        and date(copy.edit_date) between '2014-07-01' and '2015-06-30'
        GROUP BY 2
) a
GROUP BY 2
ORDER BY 2
;


/*
------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------
*/
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 rogan.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) <= '2015-06-30'
        AND COPY.audio = TRUE
        GROUP BY 2
UNION ALL
        SELECT COALESCE(COUNT(DISTINCT COPY.id),0) AS copy_id, org2.shortname AS org2_shortname
        FROM rogan.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) <= '2015-06-30'
        AND date(COPY.edit_date) >= '2014-07-01' AND date(COPY.edit_date) <= '2015-06-30'
        AND COPY.audio = TRUE
        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 rogan.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) <= '2015-06-30'
        AND COPY.video = TRUE
        GROUP BY 2
UNION ALL
        SELECT COALESCE(COUNT(DISTINCT COPY.id),0) AS copy_id, org2.shortname AS org2_shortname
        FROM rogan.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) <= '2015-06-30'
        AND COPY.video = TRUE
        and date(copy.edit_date) between '2014-07-01' and '2015-06-30'
        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 rogan.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 date(COPY.create_date) >= '2014-07-01' AND date(COPY.create_date) <= '2015-06-30'
        AND COPY.video = TRUE
        GROUP BY 2
) v
LEFT JOIN (
        SELECT COALESCE(COUNT(DISTINCT COPY.id),0) AS books, org2.shortname AS shortname
        FROM rogan.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 date(COPY.create_date) >= '2014-07-01' AND date(COPY.create_date) <= '2015-06-30'
                AND COPY.book = TRUE
        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 rogan.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 date(COPY.create_date) >= '2014-07-01' AND date(COPY.create_date) <= '2015-06-30'
                AND COPY.audio = TRUE
        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 rogan.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 date(COPY.create_date) >= '2014-07-01' AND date(COPY.create_date) <= '2015-06-30'
                AND COPY.serial = TRUE
        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 rogan.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.edit_date) >= '2014-07-01' AND date(COPY.edit_date) <= '2015-06-30'
                AND COPY.video = TRUE
        GROUP BY 2
) v
LEFT JOIN (
        SELECT COALESCE(COUNT(DISTINCT COPY.id),0) AS books, org2.shortname AS shortname
        FROM rogan.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.edit_date) >= '2014-07-01' AND date(COPY.edit_date) <= '2015-06-30'
                AND COPY.book = TRUE
        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 rogan.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.edit_date) >= '2014-07-01' AND date(COPY.edit_date) <= '2015-06-30'
                AND COPY.audio = TRUE
        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 rogan.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.edit_date) >= '2014-07-01' AND date(COPY.edit_date) <= '2015-06-30'
                AND COPY.serial = TRUE
        GROUP BY 2
) e ON e.shortname = v.shortname
GROUP BY 1
ORDER BY 1
;