====== Hold Reports ======

===== Requests (Holds) Purchase Alert Report =====
**Submission Notes:** This report was submitted on 5/19/10 by Amy Terlaga of Bibliomation, Inc. It was originally created by Dan Scott of Laurentian University.

**Version**:  Evergreen v.1.6.0.2

**Data Sources Used**:  

**Running Tips/Guidelines**:  This report is designed to show those titles with patron requests against them greater than a specific number.  Libraries can use this report to determine whether or not they should purchase another copy of the title(s) in question.  The number of requests is based on individual library and not across the entire library system.

The report here is shown with number of requests greater than 5.  You can change the 5 to whatever value you desired 
**- HAVING count(all_holds.bib_id) > 5**

<code sql>

 SELECT all_holds.bib_id, aou.name, rmsr.title, rmsr.author, count(all_holds.bib_id) AS count
   FROM (( SELECT hold_request.target, hold_request.request_lib
           FROM action.hold_request
          WHERE hold_request.hold_type = 'T'::text AND hold_request.fulfillment_time IS NULL AND hold_request.cancel_time IS NULL
UNION ALL 
         SELECT bre.id, ahr.request_lib
           FROM action.hold_request ahr
      JOIN asset.call_number acn ON ahr.target = acn.id
   JOIN biblio.record_entry bre ON acn.record = bre.id
  WHERE ahr.hold_type = 'V'::text AND ahr.fulfillment_time IS NULL AND ahr.cancel_time IS NULL)
UNION ALL 
         SELECT bre.id, ahr.request_lib
           FROM action.hold_request ahr
      JOIN asset.copy ac ON ahr.target = ac.id
   JOIN asset.call_number acn ON ac.call_number = acn.id
   JOIN biblio.record_entry bre ON acn.record = bre.id
  WHERE ahr.hold_type = 'C'::text AND ahr.fulfillment_time IS NULL AND ahr.cancel_time IS NULL) all_holds(bib_id, request_lib)
   JOIN reporter.materialized_simple_record rmsr ON rmsr.id = all_holds.bib_id
   JOIN actor.org_unit aou ON aou.id = all_holds.request_lib
  GROUP BY aou.name, all_holds.bib_id, rmsr.id, rmsr.title, rmsr.author
 HAVING count(all_holds.bib_id) > 5
  ORDER BY aou.name, count(all_holds.bib_id) DESC;

</code>

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