[Koha] Help with a report query

Eric Phetteplace ephetteplace at cca.edu
Tue Sep 24 16:17:25 NZST 2019


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 at cca.edu | o 510.594.3660

5212 Broadway | Oakland, CA | 94618

:(){ :|: & };:


On Mon, Sep 23, 2019 at 5:51 PM Carlos Lopez <clopez at 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 at dml.vic.edu.au | library.dmlibrary.org.au
>
> _______________________________________________
> Koha mailing list  http://koha-community.org
> Koha at lists.katipo.co.nz
> https://lists.katipo.co.nz/mailman/listinfo/koha
>


More information about the Koha mailing list