There is a promising report on the Koha wiki (List Active Patrons by Category for a Specific Month). However, it doesn't seem to be providing valid results, even after changing "old_issues" to "issues". (The latter change produces counts in the single digits.) We're looking for a count of active borrowers between two dates, broken down by categorycode. ("Active" meaning at least one checkout between the two dates.) Appreciate any help! Cab Vinton, Director Sanbornton Public Library Sanbornton, NH
On 10 February 2011 10:20, Cab Vinton <bibliwho@gmail.com> wrote:
There is a promising report on the Koha wiki (List Active Patrons by Category for a Specific Month). However, it doesn't seem to be providing valid results, even after changing "old_issues" to "issues". (The latter change produces counts in the single digits.)
We're looking for a count of active borrowers between two dates, broken down by categorycode. ("Active" meaning at least one checkout between the two dates.)
You probably want both issues and old_issues (issues contains everything still out, but lots may have been returned in that period, so will be in the old_issues table) Chris
Hi Cab, Try this query. It uses the statistics table which contains both current and history checkouts. The query looks for any patron whose most recent checkout was on or after a specified date. The main disadvantage to this report is that it could time out if the statistics table is very big. select borrowers.surname, borrowers.firstname, borrowers.cardnumber, borrowers.categorycode, borrowers.dateexpiry from statistics, borrowers where statistics.borrowernumber = borrowers.borrowernumber and statistics.type = 'issue' group by statistics.borrowernumber having max(date(datetime)) >= 'YYYY-MM-DD' order by borrowers.surname, borrowers.firstname Bev * * * * Beverly Church LibLime Project Manager phone: 1-888-564-2457 ext. 717 1-301-654-8088 ext. 292 email: bchurch@liblime.com skype: beverlychurch On Wed, Feb 9, 2011 at 4:20 PM, Cab Vinton <bibliwho@gmail.com> wrote:
There is a promising report on the Koha wiki (List Active Patrons by Category for a Specific Month). However, it doesn't seem to be providing valid results, even after changing "old_issues" to "issues". (The latter change produces counts in the single digits.)
We're looking for a count of active borrowers between two dates, broken down by categorycode. ("Active" meaning at least one checkout between the two dates.)
Appreciate any help!
Cab Vinton, Director Sanbornton Public Library Sanbornton, NH _______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz http://lists.katipo.co.nz/mailman/listinfo/koha
Thank you, Bev! Seems to work fine for our small library. The only improvement would be to be able to specify a date range, the syntax for which isn't immediately obvious to me. Thank you again, Cab Vinton, Director Sanbornton Public Library Sanbornton, NH On Wed, Feb 9, 2011 at 4:48 PM, Church, Beverly <bchurch@liblime.com> wrote:
select borrowers.surname, borrowers.firstname, borrowers.cardnumber, borrowers.categorycode, borrowers.dateexpiry from statistics, borrowers where statistics.borrowernumber = borrowers.borrowernumber and statistics.type = 'issue' group by statistics.borrowernumber having max(date(datetime)) >= 'YYYY-MM-DD' order by borrowers.surname, borrowers.firstname
Hi Cab, Here's the query with a date range: select borrowers.surname, borrowers.firstname, borrowers.cardnumber, borrowers.categorycode, borrowers.dateexpiry from statistics, borrowers where statistics.borrowernumber = borrowers.borrowernumber and statistics.type = 'issue' group by statistics.borrowernumber having max(date(datetime)) between 'YYYY-MM-DD' and 'YYYY-MM-DD' order by borrowers.surname, borrowers.firstname Bev * * * * Beverly Church LibLime Project Manager phone: 1-888-564-2457 ext. 717 1-301-654-8088 ext. 292 email: bchurch@liblime.com skype: beverlychurch On Wed, Feb 9, 2011 at 5:11 PM, Cab Vinton <bibliwho@gmail.com> wrote:
Thank you, Bev!
Seems to work fine for our small library.
The only improvement would be to be able to specify a date range, the syntax for which isn't immediately obvious to me.
Thank you again,
Cab Vinton, Director Sanbornton Public Library Sanbornton, NH
On Wed, Feb 9, 2011 at 4:48 PM, Church, Beverly <bchurch@liblime.com> wrote:
select borrowers.surname, borrowers.firstname, borrowers.cardnumber, borrowers.categorycode, borrowers.dateexpiry from statistics, borrowers where statistics.borrowernumber = borrowers.borrowernumber and statistics.type = 'issue' group by statistics.borrowernumber having max(date(datetime)) >= 'YYYY-MM-DD' order by borrowers.surname, borrowers.firstname
participants (3)
-
Cab Vinton -
Chris Cormack -
Church, Beverly