[Koha] SQL Help - Daily (on Monthly basis) & Monthly (on Yearly basis) Transactions Counts (Circulation)

[Barton Chittenden]

Statish,

I'll do the 'month, by year'; the 'day by month' one will follow the same
pattern.

SELECT
    monthname(datetime),
    SUM( IF(type = 'issue'), 1, 0 ) as issues,
    SUM( IF(type = 'renew'), 1, 0 ) as renewals,
    SUM( IF(type = 'return'), 1, 0 ) as returns
FROM
    statistics
WHERE
    YEAR(datetime) = YEAR(current_date)
    AND MONTH(datetime) = MONTH(current_date)

I haven't actually run this; there may be typos, but it should be pretty
close.

Once you have one that works, please add it to the SQL reports library; I
found a few that were close to this, but nothing that brings it all
together quite this way.

On Mon, Mar 6, 2017 at 1:33 AM, SATISH <lis4satish at gmail.com> wrote:

> Hi,
>
> With Koha 16.5/Package Installation/ Ubuntu 14.04 Desktop /
>
> Can you please help me to prepare the Circulation report (SQL), which
> contain.
> 1* Daily and 2* Monthly Counts for Circulation.
>
> 1* Counts- Daily transactions/circulation report for a Month:
> ---------------------------------------------------------------
> example: for the month of March 2017
>
> Day | Issue | Renewals| Returns
> 01-03-2017   50       30 25
> 02-03-2017   40        25             12
>
> so on...until last date of the month
> ---------------------------------------------------------------
>
> 2* counts -  Monthly transactions/circulation report for an Year:
>
> example: for the year 2017
>
> Month |  Issues | Renewals | Returns
> January                55         45        50
> February               50         45        35
> so on .. until December
> -----------------------------------------------------------------
> As per 2* SQL, "Monthly circ in a date range"
> in Koha SQL Library gives Issue/Renewals not Returns.
>
>
> With Thanks
> Satish MV
> Librarian
> Govt. Engineering College, Hassan
> Karnataka
> www.gechassan.ac.in
> --
> _______________________________________________
> Koha mailing list  http://koha-community.org
> Koha at lists.katipo.co.nz
> https://lists.katipo.co.nz/mailman/listinfo/koha
>