Summer Reading Report

-- Summer reading report
-- this is based off copy location name because we use an internal convention within SCLENDS
-- of appropriate shelving locations starting with "Young Adult" or "Juvenile"


-- circ by juv
SELECT COUNT(circ.id) AS "Juvenile 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
JOIN asset.copy_location loc ON loc.id = circ.copy_location
WHERE date(circ.xact_start) >= '2014-06-01' AND date(circ.xact_start) <= '2014-08-15'
AND (loc.NAME iLIKE 'juv%' or loc.NAME iLIKE 'easy%' or loc.NAME iLIKE 'child%' or loc.NAME iLIKE '%parent%')
GROUP BY 2
ORDER BY 2
;

-- circ by young adult
SELECT COUNT(circ.id) AS "Young Adult Circs", 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) >= '2014-06-01' AND date(circ.xact_start) <= '2014-08-15'
AND loc.NAME LIKE 'You%'
GROUP BY 2
ORDER BY 2
;

-- circ by adult
SELECT COUNT(circ.id) AS "Adult 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
JOIN asset.copy_location loc ON loc.id = circ.copy_location
WHERE date(circ.xact_start) >= '2014-06-01' AND date(circ.xact_start) <= '2014-08-15'
AND loc.NAME LIKE 'Adu%'
GROUP BY 2
ORDER BY 2
;