Koha Reports : Patrons with outstanding fines without books

This SQL lists patrons who have outstanding fines and have returned all items belonging to the library. These reports have been tested on Koha 3.16 to koha 3.18


SELECT
borrowers.cardnumber AS Cardnumber,
CONCAT(
borrowers.surname,
', ',
borrowers.firstname) AS Name,
FORMAT(SUM(accountlines.amountoutstanding),2) AS "Amount outstanding"
FROM borrowers
JOIN accountlines
USING(borrowernumber)
WHERE
accountlines.amountoutstanding > 0
AND
borrowers.borrowernumber NOT IN (SELECT borrowernumber FROM issues)
GROUP BY borrowers.cardnumber
ORDER BY borrowers.surname ASC

If you would like to have the option of selecting only patrons belonging to a particular branch, then the following variation of the same code will do.


SELECT
borrowers.cardnumber AS Cardnumber,
CONCAT(
borrowers.surname,
', ',
borrowers.firstname) AS Name,
FORMAT(SUM(accountlines.amountoutstanding),2) AS "Amount outstanding"
FROM borrowers
JOIN accountlines
USING(borrowernumber)
WHERE
accountlines.amountoutstanding > 0
AND 
branchcode = <<Branch|branches>>
AND
borrowers.borrowernumber NOT IN (SELECT borrowernumber FROM issues)
GROUP BY borrowers.cardnumber
ORDER BY borrowers.surname ASC

The above report will include an extra step where you will be required to select the branch you want from a drop down list consisting all branches your library has.

Otuoma Sanya

Otuoma Sanya is a full-time systems librarian, tech enthusiast and writer. His areas of interest are data mining, institutional repositories, library automation and web development using python Django.

One thought on “Koha Reports : Patrons with outstanding fines without books

Leave a Reply