Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- /*
- 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
- ;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement