evergreen-reports:sql:item
Differences
This shows you the differences between two versions of the page.
Next revision | Previous revisionNext revisionBoth sides next revision | ||
evergreen-reports:sql:item [2011/03/08 21:59] – created 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 10: | Line 62: | ||
**Version**: | **Version**: | ||
+ | |||
**Data Sources Used**: | **Data Sources Used**: | ||
+ | |||
**Running Tips/ | **Running Tips/ | ||
You do not need to change anything in this report to run it. | You do not need to change anything in this report to run it. | ||
- | -------------------------------------------------------------- | ||
- | SELECT aou.shortname, | + | <code sql> |
+ | | ||
FROM asset.copy ac | FROM asset.copy ac | ||
INNER JOIN actor.org_unit aou ON ac.circ_lib = aou.id | INNER JOIN actor.org_unit aou ON ac.circ_lib = aou.id | ||
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 33: | Line 86: | ||
**Version**: | **Version**: | ||
+ | |||
**Data Sources Used**: | **Data Sources Used**: | ||
+ | |||
**Running Tips/ | **Running Tips/ | ||
You must change ORG to the correct shortname for your organizational unit to run this report. | You must change ORG to the correct shortname for your organizational unit to run this report. | ||
- | -------------------------------------------------------------- | + | |
+ | <code sql> | ||
| | ||
FROM actor.org_unit aou | FROM actor.org_unit aou | ||
Line 46: | Line 102: | ||
AND date_trunc(' | AND date_trunc(' | ||
GROUP BY aou.name, acl.name | GROUP BY aou.name, acl.name | ||
- | |||
UNION | UNION | ||
Line 57: | Line 112: | ||
WHERE aou.shortname = **' | WHERE aou.shortname = **' | ||
AND date_trunc(' | AND date_trunc(' | ||
+ | | ||
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