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

classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

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

satishamv
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
[hidden email]
https://lists.katipo.co.nz/mailman/listinfo/koha
Reply | Threaded
Open this post in threaded view
|

Re: SQL Help - Daily (on Monthly basis) & Monthly (on Yearly basis) Transactions Counts (Circulation)

barton
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 <[hidden email]> 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
> [hidden email]
> https://lists.katipo.co.nz/mailman/listinfo/koha
>
_______________________________________________
Koha mailing list  http://koha-community.org
[hidden email]
https://lists.katipo.co.nz/mailman/listinfo/koha
Reply | Threaded
Open this post in threaded view
|

Re: SQL Help - Daily (on Monthly basis) & Monthly (on Yearly basis) Transactions Counts (Circulation)

Mark Alexander
Excerpts from Barton Chittenden's message of 2017-03-06 08:08:04 -0500:

> 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.

Pretty close!  Just some misplaced parens:

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)
_______________________________________________
Koha mailing list  http://koha-community.org
[hidden email]
https://lists.katipo.co.nz/mailman/listinfo/koha
Reply | Threaded
Open this post in threaded view
|

Re: SQL Help - Daily (on Monthly basis) & Monthly (on Yearly basis) Transactions Counts (Circulation)

barton
On Mon, Mar 6, 2017 at 8:25 AM, Mark Alexander <[hidden email]> wrote:

>
> Pretty close!  Just some misplaced parens:
>
> 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
>
>
Thanks Mark!

I've definitely done worse on a first draft ;-)
_______________________________________________
Koha mailing list  http://koha-community.org
[hidden email]
https://lists.katipo.co.nz/mailman/listinfo/koha
Reply | Threaded
Open this post in threaded view
|

Re: SQL Help - Daily (on Monthly basis) & Monthly (on Yearly basis) Transactions Counts (Circulation)

satishamv
In reply to this post by satishamv
HI

I have compiled Date wise daily transactions/circulation report which I had
asked in my previous mails. Thanks to Barton Chittenden and Mark Alexander
for quick response.
Please also advice me, how to add this in the sql wiki library.
1. Date wise daily Transactions (Issue, Renewal, Return) stats for a
specific month & year
---------------------------------------------------------------------------------------------------------------------------------------------------

SELECT
      DATE(datetime) AS DATE,
      SUM( IF(type = 'issue', 1, 0 )) as Issues,
      SUM( IF(type = 'renew', 1, 0 )) as Renewals,
      SUM( IF(type = 'return', 1, 0 )) as Returns,
      COUNT(statistics.type) AS 'Total Transactions'
FROM  statistics
WHERE YEAR(datetime) = <<Enter Year YYYY>> AND  MONTH(datetime) =
<<Enter Month MM>>
GROUP BY DATE(datetime)

------------------------------------------------------------------------------------------------------------------------------------------------
Example : YYYY = 2017, MM = 3 (for March)

Result looks like:

DATEIssuesRenewalsReturnsTotal Transactions
2017-03-01 46 0 13 59
2017-03-02 43 0 9 52
2017-03-03 80 0 2 82
2017-03-04 25 0 5 30
2017-03-06 44 102 30 176
2017-03-07 30 52 46 128
2017-03-08 50 139 55 244
and I am looking help for my second query - i.e,

2.  Monthly transactions / circulation report for an entire year :

example: for the year 2017

Month |  Issues | Renewals | Returns
----------------------------------------------------------
January                55         45        50
February               50         45        35
so on .. until December


I did compile to some extent, but request some experts to  modify sql for
me as I desired.
sql is as follows;
----------------------------------------------------------------

SELECT monthname(datetime) AS month,
       year(datetime) AS year, count(*) AS count
FROM statistics
WHERE statistics.type IN ('issue') and year(datetime) = <<Year>>
GROUP BY month(datetime)

--------------------------------------------

Example for the YYYY = 2016, results looks like:

monthyearcount
July 2016 22
August 2016 41
September 2016 1447
October 2016 538
November 2016 1174
December 2016 977


 But I am looking in following format,

Month |  Issues | Renewals | Returns
----------------------------------------------------------
January                55         45        50

Please share your ideas.


With Thanks

Satish MV
Librarian
Govt. Engineering College, Hassan
Karnataka.
www.gechassan.ac.in
_______________________________________________
Koha mailing list  http://koha-community.org
[hidden email]
https://lists.katipo.co.nz/mailman/listinfo/koha
Reply | Threaded
Open this post in threaded view
|

Re: SQL Help - Daily (on Monthly basis) & Monthly (on Yearly basis) Transactions Counts (Circulation)

satishamv
Hi,

Please find the SQL reports wiki page is updated today for

(1) Month wise circulation report (counts) for a specific year
<https://wiki.koha-community.org/wiki/SQL_Reports_Circulation#COUNT_-_Month_wise_circulation_report_for_a_specific_year>
&

(2) Date wise daily circulation report (counts) for a specific month of an
year
<https://wiki.koha-community.org/wiki/SQL_Reports_Circulation#COUNT_-_Date_wise_daily_circulation_report_for_specific_month_of_an_year>

With Thanks

Satish MV
Librarian
Govt. Engineering College, Hassan
Karnataka.
www.gechassan.ac.in
_______________________________________________
Koha mailing list  http://koha-community.org
[hidden email]
https://lists.katipo.co.nz/mailman/listinfo/koha
Reply | Threaded
Open this post in threaded view
|

Re: SQL Help - Daily (on Monthly basis) & Monthly (on Yearly basis) Transactions Counts (Circulation)

barton
On Mar 10, 2017 23:06, "SATISH" <[hidden email]> wrote:

Hi,

Please find the SQL reports wiki page is updated today


Excellent!

Thanks for adding these!

--Barton
_______________________________________________
Koha mailing list  http://koha-community.org
[hidden email]
https://lists.katipo.co.nz/mailman/listinfo/koha