Differences

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

--- evergreen-reports:ill_stat_reports [2010/06/08 12:25] – terlaga
+++ evergreen-reports:ill_stat_reports [2011/03/08 22:07] (current) – removed bshum
@@ Line 1: / Line 1: @@
-====== Interlibrary Loan Stats ======
-===== Monthly Interlibrary Loan Stats by Circulating Library =====
-**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 and modified to include the total.
-**Version**:  Evergreen v.1.6.0.2
-**Data Sources Used**:
-**Running Tips/Guidelines**:  This report will give you a monthly count of those items coming from other libraries that circulate at your library.  The breakdown is by shelving location.  You can run this report at any time during the month following the month for which you would like statistics.
-You will need to change **ORG** in both parts of the query to the shortname of the organizational unit in question.
---------------------------------------------------------------
-SELECT aou.name AS "Circulating Library", aou2.name AS "Owning Library", acl.name AS "Shelving Location", COUNT(acirc.id) AS "COUNT"
-FROM action.circulation acirc
-  INNER JOIN actor.org_unit aou ON acirc.circ_lib = aou.id
-  INNER JOIN asset.copy ac ON acirc.target_copy = ac.id
-  INNER JOIN asset.call_number acn ON ac.call_number = acn.id
-  INNER JOIN asset.copy_location acl ON ac.location = acl.id
-  INNER JOIN actor.org_unit aou2 ON aou2.id = acl.owning_lib
-WHERE acirc.circ_lib != acn.owning_lib AND aou.shortname = **'ORG'** AND DATE_TRUNC('MONTH'::text, acirc.create_time) = DATE_TRUNC('MONTH'::text, NOW() - '1 mon'::interval)
-AND acirc.desk_renewal IS FALSE AND acirc.opac_renewal IS FALSE AND acirc.phone_renewal IS FALSE AND ac.call_number != '-1'
-GROUP by aou.name, aou2.name, acl.name
-UNION
-SELECT 'ZZZ Total' AS "Circulating Library", '' AS "Owning Library", '' AS "Shelving Location", COUNT(acirc.id) AS "COUNT"
-FROM action.circulation acirc
-  INNER JOIN actor.org_unit aou ON acirc.circ_lib = aou.id
-  INNER JOIN asset.copy ac ON acirc.target_copy = ac.id
-  INNER JOIN asset.call_number acn ON ac.call_number = acn.id
-  INNER JOIN asset.copy_location acl ON ac.location = acl.id
-  INNER JOIN actor.org_unit aou2 ON aou2.id = acl.owning_lib
-WHERE acirc.circ_lib != acn.owning_lib AND aou.shortname = **'ORG'** AND DATE_TRUNC('MONTH'::text, acirc.create_time) = DATE_TRUNC('MONTH'::text, NOW() - '1 mon'::interval)
-AND acirc.desk_renewal IS FALSE AND acirc.opac_renewal IS FALSE AND acirc.phone_renewal IS FALSE AND ac.call_number != '-1'
-ORDER BY 1, 2, 3;