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
Koha@lists.katipo.co.nz
https://lists.katipo.co.nz/mailman/listinfo/koha
