Evergreen DokuWiki

User Tools

Site Tools

You are here: start » evergreen-reports » sql » item
evergreen-reports:sql:item

Differences

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

Link to this comparison view

Next revision		Previous revision
evergreen-reports:sql:item [2011/03/08 21:59] – created bshumevergreen-reports:sql:item [2022/02/10 13:34] (current) – external edit 127.0.0.1
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**:  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 ===== ===== 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**:  Evergreen v.1.6.0.2 **Version**:  Evergreen v.1.6.0.2
 +
 **Data Sources Used**:   **Data Sources Used**:  
 +
 **Running Tips/Guidelines**:  This report is perfect for consortium/library staff to assist in testing purposes.  It will provide a representative item barcode per circulation modifier in the Evergreen system so that circulation rules can be tested.   **Running Tips/Guidelines**:  This report is perfect for consortium/library staff to assist in testing purposes.  It will provide a representative item barcode per circulation modifier in the Evergreen system so that circulation rules can be tested.  
  
 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, ac.circ_modifier, MAX(ac.barcode)+<code sql> 
 +  SELECT aou.shortname, ac.circ_modifier, MAX(ac.barcode)
   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, circ_modifier   GROUP BY aou.shortname, circ_modifier
-  ORDER BY aou.shortname, ac.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>
  
 ===== Item Statistical Reports ===== ===== Item Statistical Reports =====
Line 33: Line 116:
  
 **Version**:  Evergreen v.1.6.0.2 **Version**:  Evergreen v.1.6.0.2
 +
 **Data Sources Used**:   **Data Sources Used**:  
 +
 **Running Tips/Guidelines**:  This report will provide a monthly count of those items added by a specific organizational unit, with a breakdown by shelving location.   **Running Tips/Guidelines**:  This report will provide a monthly count of those items added by a specific organizational unit, with a breakdown by shelving location.  
  
 You must change ORG to the correct shortname for your organizational unit to run this report.  It can be run at any time during the month following the month for which you are interested in gathering statistics. You must change ORG to the correct shortname for your organizational unit to run this report.  It can be run at any time during the month following the month for which you are interested in gathering statistics.
---------------------------------------------------------------+ 
 +<code sql>
  SELECT aou.name AS "Library", acl.name AS "Shelving Location", count(ac.barcode) AS "Count"  SELECT aou.name AS "Library", acl.name AS "Shelving Location", count(ac.barcode) AS "Count"
    FROM actor.org_unit aou    FROM actor.org_unit aou
Line 46: Line 132:
   AND date_trunc('MONTH'::text, ac.create_date) = date_trunc('MONTH'::text, now() - '1 mon'::interval)   AND date_trunc('MONTH'::text, ac.create_date) = date_trunc('MONTH'::text, now() - '1 mon'::interval)
   GROUP BY aou.name, acl.name   GROUP BY aou.name, acl.name
- 
  
 UNION UNION
Line 57: Line 142:
   WHERE aou.shortname = **'ORG'**::text AND ac.deleted IS FALSE AND acn.deleted IS FALSE    WHERE aou.shortname = **'ORG'**::text AND ac.deleted IS FALSE AND acn.deleted IS FALSE 
   AND date_trunc('MONTH'::text, ac.create_date) = date_trunc('MONTH'::text, now() - '1 mon'::interval)   AND date_trunc('MONTH'::text, ac.create_date) = date_trunc('MONTH'::text, now() - '1 mon'::interval)
 +  
   ORDER BY 1, 2, 3;   ORDER BY 1, 2, 3;
- +</code>
---------------------------------------- +
  
 Back to [[evergreen-reports:sql|Evergreen SQL Report Submissions]] main page Back to [[evergreen-reports:sql|Evergreen SQL Report Submissions]] main page
- 
evergreen-reports/sql/item.1299639588.txt.gz · Last modified: 2022/02/10 13:33 (external edit)
Except where otherwise noted, content on this wiki is licensed under the following license: CC Attribution-Share Alike 4.0 International
CC Attribution-Share Alike 4.0 International Donate Powered by PHP Valid HTML5 Valid CSS Driven by DokuWiki

© 2008-2022 GPLS and others. Evergreen is open source software, freely licensed under GNU GPLv2 or later.
The Evergreen Project is a U.S. 501(c)3 non-profit organization.