Weeding Reports

-- this is the manually run report that shows circ count
SELECT COUNT(acirc.id), ac.barcode AS "barcode", acl.NAME AS "shelving location",
acn.label AS "call number", msr.title, MAX(acirc.xact_start) AS "last circulation"
FROM asset.COPY ac
JOIN asset.copy_location acl ON ac.LOCATION = acl.id
LEFT JOIN ACTION.circulation acirc ON acirc.target_copy = ac.id
JOIN asset.call_number acn ON acn.id = ac.call_number
JOIN reporter.materialized_simple_record msr ON msr.id = acn.record
JOIN actor.org_unit child ON child.id = ac.circ_lib
JOIN actor.org_unit parent ON parent.id = child.parent_ou
WHERE acl.NAME ILIKE 'Adult Fiction'
AND acirc.xact_start IS NULL
AND date(ac.create_date) <= NOW() - INTERVAL '3 years'
AND child.shortname = 'YCL-RH'
AND ac.deleted = FALSE
and ac.status = 0
GROUP BY 2, 3, 4, 5
UNION ALL
SELECT COUNT(acirc.id), ac.barcode AS "barcode", acl.NAME AS "shelving location",
acn.label AS "call number", msr.title, MAX(acirc.xact_start) AS "last circulation"
FROM asset.COPY ac
JOIN asset.copy_location acl ON ac.LOCATION = acl.id
LEFT JOIN ACTION.circulation acirc ON acirc.target_copy = ac.id
JOIN asset.call_number acn ON acn.id = ac.call_number
JOIN reporter.materialized_simple_record msr ON msr.id = acn.record
JOIN actor.org_unit child ON child.id = ac.circ_lib
JOIN actor.org_unit parent ON parent.id = child.parent_ou
WHERE acl.NAME ILIKE 'Adult Fiction'
AND date(ac.create_date) <= NOW() - INTERVAL '3 years'
AND child.shortname = 'YCL-RH'
AND ac.deleted = FALSE
and ac.status = 0
GROUP BY 2, 3, 4, 5
HAVING MAX(acirc.xact_start) < NOW() - INTERVAL '3 years'
ORDER BY 4
;