Koha reports : patrons with outstanding overdue fines

This is a simple report that displays all patrons with outstanding overdue fines in a given date range.
NB: This SQL code only runs in Koha reports module.


SELECT
  borrowers.cardnumber,
        CONCAT(borrowers.surname,  ', ', borrowers.firstname) AS Name, CONCAT(phone,' || ',mobile) AS Contact,
  FORMAT(SUM(accountlines.amountoutstanding),2) AS "Amount due"
FROM
  borrowers
JOIN
  accountlines
USING(borrowernumber)
WHERE
  accountlines.amountoutstanding > 0 AND accountlines.timestamp BETWEEN <<Between::|date>> AND <<And::|date>>
GROUP BY borrowers.cardnumber 
ORDER BY borrowers.surname ASC

Do you need a custom report from Koha? Contact me now. It only costs a cup of coffee!

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.

Leave a Reply