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:38] – 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 =====
@@ Line 23: / Line 76: @@
   GROUP BY aou.shortname, circ_modifier
   ORDER BY aou.shortname, ac.circ_modifier;
+</code>
+==== Sample Item Barcode from each Circulation Modifier and Org Unit ====
+**Submission Notes:**
+This report was submitted on 6/12/2015 by Rogan Hamby of SC LENDS. It was created by Rogan Hamby.
+**Version**:  Evergreen 2.1, 2.5, 2.7
+**Data Sources Used**:
+**Running Tips/Guidelines**:  This is for testing in library consortiums / systems where you need items from different org units for testing org unit level rules. It additional requires that the items be available and not deleted.  You do not need to change anything in this report to run it.
+<code sql>
+SELECT DISTINCT ON (ac.circ_modifier, paou.shortname) ac.barcode, ac.id AS "item id", ac.circ_modifier, aou.id AS "org id", aou.shortname
+FROM asset.COPY ac
+JOIN asset.copy_location acl ON acl.id = ac.LOCATION
+JOIN actor.org_unit aou ON aou.id = ac.circ_lib
+JOIN actor.org_unit paou ON paou.id = aou.parent_ou
+WHERE ac.deleted = FALSE AND ac.status IN (0,7) AND ac.circulate = TRUE AND acl.circulate = TRUE AND aou.ou_type = 3
+UNION ALL
+SELECT DISTINCT ON (ac.circ_modifier, paou.shortname) ac.barcode, ac.id AS "item id", ac.circ_modifier, aou.id AS "org id", aou.shortname
+FROM asset.COPY ac
+JOIN asset.copy_location acl ON acl.id = ac.LOCATION
+JOIN actor.org_unit aou ON aou.id = ac.circ_lib
+JOIN actor.org_unit paou ON paou.id = aou.parent_ou
+WHERE ac.deleted = FALSE AND ac.status IN (0,7) AND ac.circulate = TRUE AND acl.circulate = TRUE AND aou.ou_type = 5
+;
 </code>
@@ Line 64: / Line 147: @@
 Back to [[evergreen-reports:sql|Evergreen SQL Report Submissions]] main page