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.

<pre>

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;</pre>

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.

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;

Back to Evergreen SQL Report Submissions main page