evergreen-reports:sql:item
Differences
This shows you the differences between two versions of the page.
Both sides previous revisionPrevious revisionNext revision | Previous revisionNext revisionBoth sides next revision | ||
evergreen-reports:sql:item [2011/05/08 13:59] – employ use of <code sql> tagging bshum | evergreen-reports:sql:item [2015/06/12 11:20] – roganh | ||
---|---|---|---|
Line 1: | Line 1: | ||
====== ITEM REPORTS ====== | ====== ITEM REPORTS ====== | ||
+ | |||
+ | ===== Weeding Reports ===== | ||
+ | |||
+ | **Submission Notes:** | ||
+ | This report was submitted on 6/12/15 by Rogan Hamby of SC LENDS. It was created by Rogan Hamby. | ||
+ | |||
+ | **Version**: | ||
+ | |||
+ | **Data Sources Used**: all standard tables | ||
+ | |||
+ | **Purpose**: | ||
+ | |||
+ | **Running Tips/ | ||
+ | |||
+ | **Notes**: It could probably be streamlined but it works well and I've been happy with it so far. | ||
+ | |||
+ | <code sql> | ||
+ | SELECT COUNT(acirc.id), | ||
+ | acn.label AS "call number", | ||
+ | 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 ' | ||
+ | AND acirc.xact_start IS NULL | ||
+ | AND date(ac.create_date) <= NOW() - INTERVAL '3 years' | ||
+ | AND child.shortname = ' | ||
+ | AND ac.deleted = FALSE | ||
+ | AND ac.status = 0 | ||
+ | GROUP BY 2, 3, 4, 5 | ||
+ | UNION ALL | ||
+ | SELECT COUNT(acirc.id), | ||
+ | acn.label AS "call number", | ||
+ | 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 ' | ||
+ | AND date(ac.create_date) <= NOW() - INTERVAL '3 years' | ||
+ | AND child.shortname = ' | ||
+ | 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 | ||
+ | ; | ||
+ | </ | ||
===== Item List Reports ===== | ===== Item List Reports ===== | ||
==== Sample Item Barcode from each Circulation Modifier ==== | ==== Sample Item Barcode from each Circulation Modifier ==== | ||
- | |||
**Submission Notes:** | **Submission Notes:** | ||
Line 23: | Line 75: | ||
WHERE ac.deleted IS FALSE | WHERE ac.deleted IS FALSE | ||
GROUP BY aou.shortname, | GROUP BY aou.shortname, | ||
- | ORDER BY aou.shortname, | + | ORDER BY aou.shortname, |
- | + | </ | |
===== Item Statistical Reports ===== | ===== Item Statistical Reports ===== | ||
Line 35: | Line 86: | ||
**Version**: | **Version**: | ||
+ | |||
**Data Sources Used**: | **Data Sources Used**: | ||
+ | |||
**Running Tips/ | **Running Tips/ | ||
Line 41: | Line 94: | ||
<code sql> | <code sql> | ||
- | |||
| | ||
FROM actor.org_unit aou | FROM actor.org_unit aou | ||
Line 62: | Line 114: | ||
| | ||
ORDER BY 1, 2, 3; | ORDER BY 1, 2, 3; | ||
- | |||
</ | </ | ||
Back to [[evergreen-reports: | Back to [[evergreen-reports: | ||
evergreen-reports/sql/item.txt · Last modified: 2022/02/10 13:34 by 127.0.0.1