SQL Help - Daily (on Monthly basis) & Monthly (on Yearly basis) Transactions Counts (Circulation)
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 --
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@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@lists.katipo.co.nz https://lists.katipo.co.nz/mailman/listinfo/koha
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)
On Mon, Mar 6, 2017 at 8:25 AM, Mark Alexander <marka@pobox.com> 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 ;-)
participants (3)
-
Barton Chittenden -
Mark Alexander -
SATISH