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
[email protected]
https://lists.katipo.co.nz/mailman/listinfo/koha
--
George Williams
Next Search Catalog Coordinator
Send NEXT support e-mails to [email protected]
_______________________________________________
Koha mailing list http://koha-community.org
[email protected]
https://lists.katipo.co.nz/mailman/listinfo/koha
