Differences

This shows you the differences between two versions of the page.

--- evergreen-reports:sql:item [2011/05/08 17:35] – sylvar
+++ evergreen-reports:sql:item [2015/06/12 11:20] – roganh
@@ Line 1: / Line 1: @@
 ====== 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**:  Used on Evergreen 2.5, 2.7
+**Data Sources Used**: all standard tables
+**Purpose**: This is a weeding list by shelving location and org unit. It filters by an interval giving both items that haven't circulated in the time period at all as well as those that never have.
+**Running Tips/Guidelines**:  There a few parameters you will want to set.  Where it says 'SHORT_ORG_UNIT' inside the single quotes (but keeping the single quotes) put the short org unit name of the library that is the circulating library of the materials to remove.  Where it says 'SHELVING_LOCATION' do the same with the shelving location name.  It is not case sensitive and you can use % as wild cards.  So, if you have multiple Adult shelving locations you want to weed at once (Adult Fiction, Adult Large Print, etc...) you can put 'adult%' in there.  Finally, the interval.  The report currently uses an interval of '3 years' but that can be changed to '5 years', '20 weeks' or other reasonable intervals.
+**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), 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 'SHELVING_LOCATION'
+AND acirc.xact_start IS NULL
+AND date(ac.create_date) <= NOW() - INTERVAL '3 years'
+AND child.shortname = 'SHORT_ORG_UNIT'
+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 'SHELVING_LOCATION'
+AND date(ac.create_date) <= NOW() - INTERVAL '3 years'
+AND child.shortname = 'SHORT_ORG_UNIT'
+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
+;
+</code>
 ===== Item List Reports =====