Adding to this report from Carlos below, we have multiple campuses so I'd love to be able to run the report for each campus. I've tried adding in: AND items.homebranch=<<Home branch|branches>> (copied from another report that I can select campuses) in various spots but I keep getting error messages - so I've obviously missed something important somwhere else. Where should I insert this or similar text in the search to be able to select which campus the results are from? 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) Thanks so much for your help! And thanks Carlos for a handy report. SQL is not my strong-point... Kerrie Stevens AALIA(CP), MAppSci(LibMgt), BBus(Info&LibMgt) Librarian – Melbourne Campus HDR Liaison Librarian ALPHACRUCIS COLLEGE ------------------------------ Message: 2 Date: Mon, 23 Sep 2019 21:17:25 -0700 From: Eric Phetteplace <ephetteplace@cca.edu> To: Carlos Lopez <clopez@dml.vic.edu.au> Cc: koha <koha@lists.katipo.co.nz> Subject: Re: [Koha] Help with a report query Message-ID: <CACSkZcYX_qWtUeMJ8bTcdQoaMEz8mm7Mhjni=Jg7hdnyRXhQEQ@mail.gmail.com> Content-Type: text/plain; charset="UTF-8" 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