[Koha] Report help

[Steven Nickerson]

Hi Kerrie,
I think adding "AND s.branch =<<Branch|branches>>" will do what you're
looking for.  So the whole SQL statement would be:
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>> AND s.branch =<<Branch|branches>> GROUP BY
s.borrowernumber ORDER BY count(s.borrowernumber) DESC LIMIT 20

That is, assuming each campus library is defined as a separate "branch" in
Koha, of course.   Hope this helps!
Steve

-----Original Message-----
From: Kerrie Stevens [mailto:KStevens at harvest.edu.au] 
Sent: Tuesday, October 14, 2014 12:29 AM
To: koha at lists.katipo.co.nz
Subject: [Koha] Report help

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