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