====== 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 =====

==== Sample Item Barcode from each Circulation Modifier ====

**Submission Notes:**
This report was submitted on 5/24/10 by Amy Terlaga of Bibliomation, Inc. It was created by Dan Scott of Laurentian University.

**Version**:  Evergreen v.1.6.0.2

**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.  

You do not need to change anything in this report to run it.

<code sql>
  SELECT aou.shortname, ac.circ_modifier, MAX(ac.barcode)
  FROM asset.copy ac 
    INNER JOIN actor.org_unit aou ON ac.circ_lib = aou.id
  WHERE ac.deleted IS FALSE
  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>

===== Item Statistical Reports =====

==== Monthly Items Added by Shelving Location - COUNT ====

**Submission Notes:**
This report was submitted on 6/8/10 by Amy Terlaga of Bibliomation, Inc. It was created by Dan Scott of Laurentian University.  The report was modified to add TOTAL.

**Version**:  Evergreen v.1.6.0.2

**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.  

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"
   FROM actor.org_unit aou
   JOIN asset.call_number acn ON acn.owning_lib = aou.id
   JOIN asset.copy ac ON ac.call_number = acn.id
   JOIN asset.copy_location acl ON ac.location = acl.id
  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)
  GROUP BY aou.name, acl.name

UNION

 SELECT 'ZZZ Total' AS "Library", '' AS "Shelving Location", count(ac.barcode) AS "Count"
   FROM actor.org_unit aou
   JOIN asset.call_number acn ON acn.owning_lib = aou.id
   JOIN asset.copy ac ON ac.call_number = acn.id
   JOIN asset.copy_location acl ON ac.location = acl.id
  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)
  
  ORDER BY 1, 2, 3;
</code>

Back to [[evergreen-reports:sql|Evergreen SQL Report Submissions]] main page