[Koha] Help with a report query

[Eric Phetteplace]

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
>