====== Borrower List Reports ======

===== Borrowers with Email Addresses by Home Library =====

**Submission Notes:**
This report was created and submitted on 5/20/10 by Amy Terlaga of Bibliomation, Inc. (with assistance from Benjamin Shum)

**Version**:  Evergreen v.1.6.0.2
**Data Sources Used**:  
**Running Tips/Guidelines**:  This report is designed to list those patrons from a specific library with email addresses in their patron record.  

To run this report, change the **'ORG'** to the shortname of the home library in question.

<code sql>
SELECT actor.card.barcode, actor.usr.first_given_name, actor.usr.family_name, actor.usr.email, actor.org_unit.name
  FROM actor.usr, actor.org_unit, actor.card
  WHERE actor.usr.home_ou = actor.org_unit.id
  AND actor.card.id = actor.usr.card
  AND actor.org_unit.shortname = **'ORG'**
  AND ((actor.usr.email != '') 
  AND (actor.usr.email IS NOT NULL))
ORDER BY family_name;

</code>

===== K-12 Student List with Homeroom Stat =====

**Submission Notes:**
This report was created and submitted on 9/2/10 by Amy Terlaga of Bibliomation, Inc. (with some assistance from Mike Rylander)

**Version**:  Evergreen v.1.6.0.2
**Data Sources Used**:  
**Running Tips/Guidelines**:  This report is designed to list all borrowers from a specific k-12 library, including barcode, name, street1 address, patron profile group, and homeroom stat category.

To run this report, change the [ORG] to the shortname of the home library in question and change the [stat cat id #] to the stat cat id # for your homeroom information.
<code sql>
SELECT acard.barcode AS "Barcode", au.family_name AS "Last Name", au.first_given_name AS "First Name", aua.street1 AS "Street1", 
pgt.name AS "Group Profile", asceum.stat_cat_entry AS "Homeroom", aou.name AS "Library Name"
   FROM actor.usr au
   INNER JOIN actor.org_unit aou ON au.home_ou = aou.id
   INNER JOIN actor.card acard ON acard.id = au.card
   INNER JOIN actor.usr_address aua ON au.id = aua.usr
   INNER JOIN permission.grp_tree pgt ON au.profile = pgt.id
   LEFT OUTER JOIN actor.stat_cat_entry_usr_map asceum ON asceum.target_usr = au.id
   LEFT OUTER JOIN actor.stat_cat astat ON asceum.stat_cat = astat.id
WHERE aou.shortname = '[ORG]'::text AND asceum.stat_cat = '[stat cat id #]'

ORDER BY 4, 2, 1
</code>

Back to [[evergreen-reports:sql:borrower|Borrower Reports]] page