Differences

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

--- evergreen-reports:borrower_statistical_reports [2010/06/10 13:23] – terlaga
+++ evergreen-reports:borrower_statistical_reports [2011/03/08 22:11] (current) – removed bshum
@@ Line 1: / Line 1: @@
-====== 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.
---------------------------------------------------------------
- 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;
-------------------------------------
-Back to [[evergreen-reports:borrower_reports|Borrower Reports]] page