[Koha] Report help- reading history

[Katelyn Browne]

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 at ccpcs.org
http://www.ccpcs.org/library/

On Mon, Sep 22, 2014 at 3:58 PM, clint.deckard <
clint.deckard at 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 at frontiers.co.nz <mailto:clint.deckard at 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 at lists.katipo.co.nz <mailto:Koha at 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 at osslabs.biz <mailto:vishal.patil at osslabs.biz>
>>
>>
> _______________________________________________
> Koha mailing list  http://koha-community.org
> Koha at lists.katipo.co.nz
> http://lists.katipo.co.nz/mailman/listinfo/koha
>