One of the most requested SQL reports I frequently receive is tracking circulation and cataloging actions performed by library staff. These reports help in monitoring staff activities, ensuring accountability, and improving workflow efficiency.
In this blog post, I share two essential SQL queries: one for circulation actions by staff within a given date range and another for cataloging activities.
1. Circulation Actions by Staff (Date Range)
This SQL query retrieves details of circulation transactions (checkouts, renewals, returns, etc.) performed by staff within a specified date range. It includes staff names, user details, action types, timestamps, and item information.
SQL Query:
SELECT
CONCAT(p.firstname, ' ', p.surname) AS 'staff_name',
p.cardnumber AS 'staff_cardnumber',
a.action AS 'action',
a.timestamp AS 'time',
c.barcode AS 'barcode',
d.title AS 'title',
CONCAT(k.firstname, ' ', k.surname) AS 'user_name',
k.cardnumber AS 'user_cardnumber'
FROM action_logs a
LEFT JOIN borrowers p ON a.user = p.borrowernumber
LEFT JOIN items c ON a.info = c.itemnumber
LEFT JOIN biblio d ON c.biblionumber = d.biblionumber
LEFT JOIN borrowers k ON a.object = k.borrowernumber
WHERE a.timestamp BETWEEN <<Circulation action Between (dd/mm/yyyy)|date>>
AND <<and (dd/mm/yyyy)|date>>
AND a.module = 'CIRCULATION'
AND c.homebranch = <<Branch|branches>>;
Key Features:
- Retrieves details of all circulation transactions.
- Tracks actions like checkouts, renewals, and returns.
- Helps in monitoring staff performance in handling circulation transactions.
%20(86)%20%E2%80%BA%20Saved%20reports%20%E2%80%BA%20Guided%20reports%20wizard%20%E2%80%BA%20Reports%20%E2%80%BA%20Koha.png)
2. Books Catalogued by Staff (Date Range)
This SQL query helps library administrators track books cataloged by staff, including details like the bibliographic number, title, barcode, and timestamp of the cataloging action.
SQL Query:
SELECT
CONCAT(s.firstname, ' ', s.surname) AS 'staff_name',
s.cardnumber AS 'staff_cardnumber',
a.action AS 'action',
a.timestamp AS 'time',
b.biblionumber AS 'biblionumber',
b.title AS 'title',
i.barcode AS 'barcode',
br.branchname
FROM action_logs a
LEFT JOIN borrowers s ON a.user = s.borrowernumber
LEFT JOIN biblio b ON a.object = b.biblionumber
LEFT JOIN items i ON b.biblionumber = i.biblionumber
LEFT JOIN branches br ON s.branchcode = br.branchcode
WHERE a.timestamp BETWEEN <<Cataloguing action Between (yyyy-mm-dd)|date>>
AND <<and (yyyy-mm-dd)|date>>
AND a.module = 'CATALOGUING'
AND a.action = 'ADD'
AND i.homebranch = <<Branch|branches>>;
Key Features:
- Tracks books cataloged by staff.
- Useful for evaluating cataloging efficiency and staff performance.
- Helps in auditing new additions to the library collection
%20(240)%20%E2%80%BA%20Saved%20reports%20%E2%80%BA%20Guided%20reports%20wizard%20%E2%80%BA%20Reports%20%E2%80%BA%20Koha.png)
Koha's ability to generate insightful reports is one of the most powerful features. The above SQL queries provide an efficient way to track staff activities in circulation and cataloging, making them invaluable for library administrators.
