====== Borrower Statistical Reports ======

===== Monthly Borrowers Added by Patron Profile Group and Stat Category =====

**Submission Notes:**
This report was submitted on 6/10/10 by Amy Terlaga of Bibliomation, Inc. It was created by Dan Scott of Laurentian University.  It was modified to include TOTAL.

**Version**:  Evergreen v.1.6.0.2
**Data Sources Used**:  
**Running Tips/Guidelines**:  This report shows those borrowers registered in a given month.  You filter by organizational unit.  You must replace the shortname of your org unit for the 'ORG' you see in the query below.  The breakdown is by patron profile group and (if you use) patron statistical category.

Run this report any time in the month directly following the month you would like to capture statistics for.

<code sql>
 SELECT aou.name AS "Library", pgt.name AS "Profile", asceum.stat_cat_entry AS "Residency", count(pgt.name) AS "Count"
   FROM actor.usr au
   JOIN permission.grp_tree pgt ON au.profile = pgt.id
   JOIN actor.org_unit aou ON aou.id = au.home_ou
   LEFT JOIN actor.stat_cat_entry_usr_map asceum ON asceum.target_usr = au.id
  WHERE aou.shortname = **'ORG'**::text AND au.deleted IS FALSE 
  AND date_trunc('MONTH'::text, au.create_date) = date_trunc('MONTH'::text, now() - '1 mon'::interval) 
  AND (pgt.name <> ALL (ARRAY['Catalogers'::text, 'Circulators'::text, 'Staff'::text, 'Users'::text]))
  GROUP BY aou.name, pgt.name, asceum.stat_cat_entry

 UNION

 SELECT 'ZZZ Total' AS "Library", '' AS "Profile", '' AS "Residency", count(pgt.name) AS "Count"
   FROM actor.usr au
   JOIN permission.grp_tree pgt ON au.profile = pgt.id
   JOIN actor.org_unit aou ON aou.id = au.home_ou
   LEFT JOIN actor.stat_cat_entry_usr_map asceum ON asceum.target_usr = au.id
  WHERE aou.shortname = **'ORG'**::text AND au.deleted IS FALSE 
  AND date_trunc('MONTH'::text, au.create_date) = date_trunc('MONTH'::text, now() - '1 mon'::interval) 
  AND (pgt.name <> ALL (ARRAY['Catalogers'::text, 'Circulators'::text, 'Staff'::text, 'Users'::text]))

 ORDER BY 1, 2, 3;
</code>


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