Added this to the KOHA reports wiki. Melanie <http://www.facebook.com/pages/Salina-KS/Salina-Public-Library/91712193139?ref=s> From: koha-bounces@lists.katipo.co.nz [mailto:koha-bounces@lists.katipo.co.nz] On Behalf Of Melanie Hedgespeth Sent: Tuesday, November 03, 2009 1:50 PM To: Jesse Cc: koha@lists.katipo.co.nz Subject: Re: [Koha] Report of Active Users for a Month Thanks. I realized I wasn’t seeing all the results. On screen, I wasn’t getting the full list. Once I downloaded it then I saw the rest! Thanks. Melanie <http://www.facebook.com/pages/Salina-KS/Salina-Public-Library/91712193139?ref=s> From: Jesse [mailto:pianohacker@gmail.com] Sent: Tuesday, November 03, 2009 1:43 PM To: Melanie Hedgespeth Cc: koha@lists.katipo.co.nz Subject: Re: [Koha] Report of Active Users for a Month 2009/11/3 Melanie Hedgespeth <melanie@salpublib.org> Thanks. Does issue date refer to the date the item was checked out? I’m getting just a handful of issues so I’m off somewhere on it. Thanks. Melanie <http://www.facebook.com/pages/Salina-KS/Salina-Public-Library/91712193139?ref=s> From: Jesse [mailto:pianohacker@gmail.com] Sent: Tuesday, November 03, 2009 1:20 PM To: Melanie Hedgespeth Cc: koha@lists.katipo.co.nz Subject: Re: [Koha] Report of Active Users for a Month 2009/11/3 Melanie Hedgespeth <melanie@salpublib.org> I am trying to create a report for active patrons within a specific month, divided up by patron category. (I see you can create a patron statistics report for patron activity within “years”, but can’t get the code so I can adjust it to a month’s period.) Any tips? Thanks! Melanie Hedgespeth Tech Center Manager Salina Public Library 785.825.4624 Ext. 233 melanie@salpublib.org I assume by "active", you mean "checked out books", right? If so, you'd want something like this: SELECT YEAR(issuedate), MONTH(issuedate), categorycode, COUNT(DISTINCT borrowernumber) FROM old_issues LEFT JOIN borrowers USING (borrowernumber) GROUP BY YEAR(issuedate), MONTH(issuedate), categorycode; Because it uses old_issues, it won't include anything that's still checked out; getting around that requires either ugly subqueries or running the report with old_issues and issues and manually combining the results. -- Jesse Weaver issuedate is the date the item was checked out, yes. A quick run of the SQL seemed to work on my library's data, but the use of issuedate does mean that it won't include any data migrated from Koha 2.2; it'll only include checkouts made under Koha 3.0. Substituting issuedate for date_due or returndate will include results from Koha 2.2, but will skew the results (as these refer to the due date and date the item was returned, respectively). -- Jesse Weaver
