[Koha] Time-based Circulation Statistics

[George]

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 at 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 at nekls.org