[Koha] Top Borrowers for a given period

[Admire Mutsikiwa]

Hi

 

I have a  Children's Community Library which is trying to promote a reading
culture among its patrons by giving prizes to top borrowers. Given this
backdrop, I am interested in an SQL reports that will enable the library to
get  Top X e.g. Top 10  borrowers for a given period.

 

My first port of call was the report  Ex Heavy Borrowers by Chris Hall for
Horowhenua Library Trust, Catalyst
http://wiki.koha-community.org/wiki/SQL_Reports_Library#Ex_Heavy_Borrowers 

 

SELECT first.borrowernumber, first.firstname, first.surname,
first.cardnumber, issues_before, issues_after FROM(

SELECT borrowernumber, firstname, surname, cardnumber,
count(old_issues.itemnumber) AS issues_before

FROM borrowers JOIN old_issues

USING (borrowernumber)

WHERE issuedate > <<Beginning of first range (yyyy-mm-dd)|date>>

AND issuedate < <<End of first range (yyyy-mm-dd)|date>> 

GROUP BY old_issues.borrowernumber

) AS first

JOIN (

SELECT borrowernumber, count(old_issues.itemnumber) AS issues_after

FROM borrowers JOIN old_issues

USING (borrowernumber)

WHERE issuedate > <<Beginning of second range (yyyy-mm-dd)|date>>

AND issuedate < <<End of second range (yyyy-mm-dd)|date>> 

GROUP BY old_issues.borrowernumber

) AS second

WHERE first.borrowernumber = second.borrowernumber

AND issues_after < 20 AND issues_before > 60 -- borrowers borrowed fewer
than 20 items in the second range, and more than 60 in the first range.

 

By I think ,  I need some help on customizing it to suit my slightly
different requirements  

 

 

Kind Regards,

 

Admire Mutsikiwa (Mr)

ICT Manager

Libraries

University of Zimbabwe

Tel:+263-4-303276

Mob:+263782476883

 

"It is easy to dodge our responsibilities, but we cannot dodge the
consequences of dodging our responsibilities."  Josiah Charles Stamp
<http://www.goodreads.com/author/show/4867406.Josiah_Charles_Stamp> 

 


------
UZ at 60: Celebrating our Past, Inspiring the Future.