Hi Kerrie
You don't need to call up the 'items' table as there's a 'branch' field in the
'statistics' table, so this should work:
SELECT monthname(datetime) month, type, count(datetime) AS Count
FROM statistics
WHERE YEAR(datetime) = <<year>>
AND type IN ('issue', 'renew')
AND branch = <<Home branch|branches>>
GROUP BY month, type ORDER BY month(datetime)
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 | [email protected] | library.dmlibrary.org.au
-----Original Message-----
From: Koha <[email protected]> On Behalf Of Kerrie Stevens
Sent: Wednesday, 25 September 2019 10:46 AM
To: [email protected]
Subject: Re: [Koha] Help with a report query
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 <[email protected]>
To: Carlos Lopez <[email protected]>
Cc: koha <[email protected]>
Subject: Re: [Koha] Help with a report query
Message-ID:
<CACSkZcYX_qWtUeMJ8bTcdQoaMEz8mm7Mhjni=jg7hdnyrxh...@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)
[email protected] | o 510.594.3660
5212 Broadway | Oakland, CA | 94618
:(){ :|: & };:
On Mon, Sep 23, 2019 at 5:51 PM Carlos Lopez <[email protected]> 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 | [email protected] |
> library.dmlibrary.org.au
>
> _______________________________________________
> Koha mailing list http://koha-community.org [email protected]
> https://lists.katipo.co.nz/mailman/listinfo/koha
>
_______________________________________________
Koha mailing list http://koha-community.org
[email protected]
https://lists.katipo.co.nz/mailman/listinfo/koha
--
Click here to report this message as spam:
https://console.mailguard.com.au/ras/1WAB7VEjZH/y4mWms3BCIlyEX3MQFgoy/0.4
_______________________________________________
Koha mailing list http://koha-community.org
[email protected]
https://lists.katipo.co.nz/mailman/listinfo/koha
