Hi Carlos, Your report is actually very close to doing that already. Since you're already limiting by year in your WHERE clause, you just need to change it to accept a parameter instead of using YEAR(NOW()). So this should do want you want: SELECT monthname(datetime) month, type, count(datetime) as count FROM statistics WHERE YEAR(datetime) = <<year>> AND type IN ('issue', 'renew') GROUP BY month, type ORDER BY month(datetime) That does literally what you asked—limit by a particular calendar year—but you could also limit to within a given date range by asking for two parameters and using a "BETWEEN" WHERE clause like you mentioned earlier. So that would be like: SELECT monthname(datetime) month, type, count(datetime) as count FROM statistics WHERE datetime BETWEEN <<start|date>> AND <<finish|date>> AND type IN ('issue', 'renew') GROUP BY month, type ORDER BY month(datetime) The "|date" bit there means that the reports module will expect date values for those parameters and thus provide a datepicker when people go to run the report. I will caution that I've been advised not to use the "statistics" table, though I don't entirely know why. I would've written this report using the issues and old_issues tables. Best, ERIC PHETTEPLACE Systems Librarian (he/him) ephetteplace@cca.edu | o 510.594.3660 5212 Broadway | Oakland, CA | 94618 :(){ :|: & };: On Mon, Sep 23, 2019 at 5:51 PM Carlos Lopez <clopez@dml.vic.edu.au> wrote:
Hi folks
I have the following report query: ---- SELECT monthname(datetime) month, type, count(datetime) as count from statistics where YEAR(datetime) = YEAR(NOW()) and type in ('issue', 'renew') GROUP BY month, type ORDER BY month(datetime) ----
This works well but I've been asked to provide a mechanism to select the year to run this for (I would guess similar to the construct " DATE (b.dateexpiry) BETWEEN <<Between (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>>", except selecting only years)
Is it possible to do this? And if so, how?
With kind regards from the Dalton McCaughey Library Team
Carlos Lopez
Dalton McCaughey Library | 29 College Crescent, Parkville, VICTORIA 3052 Ph: 03 9340 8888 ext.1 | library@dml.vic.edu.au | library.dmlibrary.org.au
_______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz https://lists.katipo.co.nz/mailman/listinfo/koha