[Koha] Help with a report query

[Kerrie Stevens]

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 at cca.edu>
To: Carlos Lopez <clopez at dml.vic.edu.au>
Cc: koha <koha at lists.katipo.co.nz>
Subject: Re: [Koha] Help with a report query
Message-ID:
	<CACSkZcYX_qWtUeMJ8bTcdQoaMEz8mm7Mhjni=Jg7hdnyRXhQEQ at 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 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
>