Clint, This is SUPER rough and cobbled-together, but it's the best I could do in a few minutes. If I have time later, I'll try to clean it up a bit (or others are welcome to do so). I have NOT tested it very extensively! It doesn't show multiple checkouts of the same item by the same patron, but otherwise it should do basically what you're asking. SELECT topten.name, biblio.title, biblio.author FROM statistics LEFT JOIN items ON (items.itemnumber = statistics.itemnumber) LEFT JOIN biblio ON (items.biblionumber = biblio.biblionumber) LEFT JOIN (SELECT CONCAT(p.surname, ", ", p.firstname) AS name, p.borrowernumber FROM statistics s LEFT JOIN borrowers p ON (s.borrowernumber=p.borrowernumber) WHERE DATE(s.datetime) BETWEEN <<Top checkouts BETWEEN (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>> AND s.itemnumber IS NOT NULL GROUP BY p.borrowernumber ORDER BY COUNT(s.borrowernumber) DESC LIMIT 10) topten ON (topten.borrowernumber = statistics.borrowernumber) WHERE topten.name IS NOT NULL AND statistics.datetime BETWEEN <<Enter same start date (yyyy-mm-dd)|date>> AND <<and same end date (yyyy-mm-dd)|date>> AND statistics.type = "issue" ORDER BY topten.name ASC Katelyn Browne Middle/High School Librarian Capital City Public Charter School 100 Peabody Street NW Washington, DC 20011 (202) 387-0309 x1745 kbrowne@ccpcs.org http://www.ccpcs.org/library/ On Mon, Sep 22, 2014 at 3:58 PM, clint.deckard < clint.deckard@frontiers.co.nz> wrote:
Dear Vishal Patil, thank you for the assistance. The school did use the 'Patrons checking out the most' report and then copied and paste the reading history into a spreadsheet to produce the information they needed, it's just that they were hoping for a solution that required a little less intervention. I should have provided more information in my initial request for help. My apologies.
I appreciate you taking the time to help. Best wishes, Clint.
vishal patil wrote:
Hii,
Visit koha report library for any sql report:
http://wiki.koha-community.org/wiki/SQL_Reports_Library
On Mon, Sep 22, 2014 at 12:38 PM, clint.deckard < clint.deckard@frontiers.co.nz <mailto:clint.deckard@frontiers.co.nz>> wrote:
A school I deal with would like a report that lists the reading history of the top 10 borrowers so that they can award a prize to each. As I am not a sql expert I would appreciate some assistance. Much appreciated, Clint.
-- Clint Deckard Takapau Central Hawkes Bay, New Zealand.
_________________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz <mailto:Koha@lists.katipo.co.nz> http://lists.katipo.co.nz/__mailman/listinfo/koha <http://lists.katipo.co.nz/mailman/listinfo/koha>
-- *Warm Regards, Vishal Patil *Software Engineer - Nucsoft Ltd. phone: 022 32080161 | mob: 08767505528 www.osslabs.biz <http://www.osslabs.biz/> www.nucsoft.com <http://www.nucsoft.com/> vishal.patil@osslabs.biz <mailto:vishal.patil@osslabs.biz>
_______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz http://lists.katipo.co.nz/mailman/listinfo/koha