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

<code sql>

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;

</code>

Back to [[evergreen-reports:sql:circulation|Evergreen Circulation Reports]] page