Below is a query I developed to support library collection development operations using the Koha Integrated Library System and MariaDB, the variant of SQL used by Koha. The goal was to determine which popular titles (those with significant circulation in recent months) were now completely gone from the collection (having been lost, misplaced, or simply not returned) and hence might need to be replenished.
The query reports bibliographic information for each title, total circulation over a given number of months prior to the current date, and a breakdown of circulation indicating how many times the title was checked out (issued), renewed, or used locally in-house. It also provides a count of how many item records of the title exist in the catalog. Since the report only returns titles where all copies have been lost, this would provide the inventory for the number of items missing from each title.
Koha also allows for the introduction of parametrized values into the user interface, so that those running the report can optionally filter the results according to authorized values existing within the catalog.
The task of determining whether all copies of a given title had gone missing was perhaps the most challenging, since each title can refer to multiple items existing in different bibliographic records and perhaps even different expressions or formats of the same work. My solution was to test each item using a subquery to determine whether it was in the set of items that were not lost, then count those for each title. If the number of items not lost for a given title is equal to 0, then it follows that that all copies are lost for that title and it should be returned by the query.
SELECT items.itemcallnumber AS Call_Number, biblio.title AS Title, biblio.author AS Author, biblio.copyrightdate AS Copyright, count(statistics.datetime) AS Total_Circulation,
–Subdivide circulation statistics according to type.
count(CASE WHEN statistics.type = ‘issue’ THEN statistics.datetime END) AS Checkouts,
count(CASE WHEN statistics.type = ‘renew’ THEN statistics.datetime END) AS Renewals,
count(CASE WHEN statistics.type = ‘localuse’ THEN statistics.datetime END) AS Local_Use,
count(DISTINCT items.itemnumber) AS Number_of_Copies_Missing
FROM statistics
–Join to collect bibliographic information and determine missing items
LEFT JOIN items ON (items.itemnumber = statistics.itemnumber)
LEFT JOIN biblio ON (biblio.biblionumber = items.biblionumber)
–Filters and parametrizations
WHERE statistics.type IN (‘issue’, ‘localuse’, ‘renew’)
AND statistics.datetime > now() – interval <> month
AND IF(<>,items.location=<>,1)
AND IF(<>,items.coded_location_qualifier=<>,1)
AND IF(<>,items.ccode=<>,1)
GROUP BY Title
–Limit to titles where all copies are lost.
HAVING count(CASE WHEN items.itemnumber IN (SELECT items.itemnumber FROM items WHERE items.itemlost = 0) THEN items.itemnumber ELSE NULL END) = 0
ORDER BY Total_Circulation desc, items.itemcallnumber