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