Admire, This is what I make available to the libraries in our system to show hourly circulation activity during the previous calendar month - but with a slight exception. Where this example uses "branchess.branchcode LIKE '%'" I use "branchess.branchcode LIKE <<Choose your library|branches>>" because we have 51 libraries using the same system. If you've got more than one library, you can also change '%' to '<<Choose your library|branches>>' so you're only seeing 1 library at a time in the results. George --- SELECT branchess.branchname, ALL_STATS.DATE, ALL_STATS.DAY, Concat(ALL_STATS.HOUR_OF_DAY, ":00 - ", ALL_STATS.HOUR_OF_DAY, ":59") AS HOUR, CKO.COUNT AS CKO, RENEWALS.COUNT AS RENEW, RETURNS.COUNT AS RETURNS, ALL_STATS.COUNT AS TOTAL FROM (SELECT branches.branchcode, branches.branchname FROM branches) branchess LEFT JOIN (SELECT statistics.branch, DayName(statistics.datetime) AS DAY, Date_Format(statistics.datetime, '%Y-%m-%d') AS DATE, Hour(statistics.datetime) AS HOUR_OF_DAY, count(*) AS COUNT FROM statistics WHERE (statistics.type = 'issue' OR statistics.type = 'renew' OR statistics.type = 'return') AND Month(statistics.datetime) = Month(Now() - INTERVAL 1 MONTH) AND Year(statistics.datetime) = Year(Now() - INTERVAL 1 MONTH) GROUP BY statistics.branch, DayName(statistics.datetime), Date_Format(statistics.datetime, '%Y-%m-%d'), Hour(statistics.datetime)) ALL_STATS ON ALL_STATS.branch = branchess.branchcode LEFT JOIN (SELECT statistics.branch, DayName(statistics.datetime) AS DAY, Date_Format(statistics.datetime, '%Y-%m-%d') AS DATE, Hour(statistics.datetime) AS HOUR_OF_DAY, count(*) AS COUNT FROM statistics WHERE statistics.type = 'return' AND Month(statistics.datetime) = Month(Now() - INTERVAL 1 MONTH) AND Year(statistics.datetime) = Year(Now() - INTERVAL 1 MONTH) GROUP BY statistics.branch, DayName(statistics.datetime), Date_Format(statistics.datetime, '%Y-%m-%d'), Hour(statistics.datetime)) RETURNS ON RETURNS.branch = branchess.branchcode AND RETURNS.DATE = ALL_STATS.DATE AND RETURNS.HOUR_OF_DAY = ALL_STATS.HOUR_OF_DAY LEFT JOIN (SELECT statistics.branch, DayName(statistics.datetime) AS DAY, Date_Format(statistics.datetime, '%Y-%m-%d') AS DATE, Hour(statistics.datetime) AS HOUR_OF_DAY, count(*) AS COUNT FROM statistics WHERE statistics.type = 'issue' AND Month(statistics.datetime) = Month(Now() - INTERVAL 1 MONTH) AND Year(statistics.datetime) = Year(Now() - INTERVAL 1 MONTH) GROUP BY statistics.branch, DayName(statistics.datetime), Date_Format(statistics.datetime, '%Y-%m-%d'), Hour(statistics.datetime)) CKO ON CKO.branch = branchess.branchcode AND CKO.DATE = ALL_STATS.DATE AND CKO.HOUR_OF_DAY = ALL_STATS.HOUR_OF_DAY LEFT JOIN (SELECT statistics.branch, DayName(statistics.datetime) AS DAY, Date_Format(statistics.datetime, '%Y-%m-%d') AS DATE, Hour(statistics.datetime) AS HOUR_OF_DAY, count(*) AS COUNT FROM statistics WHERE statistics.type = 'renew' AND Month(statistics.datetime) = Month(Now() - INTERVAL 1 MONTH) AND Year(statistics.datetime) = Year(Now() - INTERVAL 1 MONTH) GROUP BY statistics.branch, DayName(statistics.datetime), Date_Format(statistics.datetime, '%Y-%m-%d'), Hour(statistics.datetime)) RENEWALS ON RENEWALS.branch = branchess.branchcode AND RENEWALS.DATE = ALL_STATS.DATE AND RENEWALS.HOUR_OF_DAY = ALL_STATS.HOUR_OF_DAY WHERE branchess.branchcode LIKE '%' GROUP BY branchess.branchname, ALL_STATS.DATE, ALL_STATS.DAY, Concat(ALL_STATS.HOUR_OF_DAY, ":00 - ", ALL_STATS.HOUR_OF_DAY, ":59"), CKO.COUNT, RENEWALS.COUNT, RETURNS.COUNT, ALL_STATS.COUNT, ALL_STATS.HOUR_OF_DAY ORDER BY branchess.branchname, ALL_STATS.DATE, ALL_STATS.DAY, ALL_STATS.HOUR_OF_DAY --- On 9/1/2019 8:47 AM, Admire Mutsikiwa wrote:
Hi
We are interested in generating circulation statistics grouping then by hour of day so that we can establish the busiest times for circulation activities. I will appreciate any pointers on coming with such a SQL report.
Kind Regards,
Admire Mutsikiwa _______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz https://lists.katipo.co.nz/mailman/listinfo/koha
-- George Williams Next Search Catalog Coordinator Send NEXT support e-mails to nexthelp@nekls.org