[Koha] Report help

[Heather Braum (NEKLS)]

Kerrie,

You need to add the parameter of b.branchcode=<<choose branch|branches>> in
the WHERE part of your SQL statement, like I did below. The {choose branch}
part is actually arbitrary and can be whatever you want. Try this:

SELECT concat(b.surname,', ',b.firstname) AS name, count(s.borrowernumber)
AS checkouts
FROM statistics s
LEFT JOIN borrowers b
USING (borrowernumber)
WHERE b.branchcode=<<chopse branch|branchces>> AND s.datetime BETWEEN <<Top
checkouts BETWEEN (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>>
GROUP BY s.borrowernumber
ORDER BY count(s.borrowernumber) DESC
LIMIT 20

Hope it helps and works!

Heather Braum
NExpress Coordinator
Resource Sharing Librarian
Northeast Kansas Library System
hbraum at nekls.org

"The illiterate of the 21st century will not be those who cannot read
and write, but those who cannot learn, unlearn, and relearn." ~Alvin
Toffler, *Rethinking the Future*




On Mon, Oct 13, 2014 at 11:29 PM, Kerrie Stevens <KStevens at harvest.edu.au>
wrote:

> Hi Everyone,
> I've been using the report 'Patrons with most checkouts in date range'
> with success, but when I try to modify it to allow me to select which
> campus library the patrons are from so I can get a top 20 list for any of
> our campus libraries, I can't get it to work - can anyone help me with this?
> SELECT concat(b.surname,', ',b.firstname) AS name, count(s.borrowernumber)
> AS checkouts
> FROM statistics s
> LEFT JOIN borrowers b
> USING (borrowernumber)
> WHERE s.datetime BETWEEN <<Top checkouts BETWEEN (yyyy-mm-dd)|date>> AND
> <<and (yyyy-mm-dd)|date>>
> GROUP BY s.borrowernumber
> ORDER BY count(s.borrowernumber) DESC
> LIMIT 20
> Thank you!
> Kerrie Stevens
> COLLEGE LIBRARIAN
> Harvest Bible College
> _______________________________________________
> Koha mailing list  http://koha-community.org
> Koha at lists.katipo.co.nz
> http://lists.katipo.co.nz/mailman/listinfo/koha
>