I compared some of my sql statements for statistics with the results from Koha's built in reports in the reports module. Currently the 'Patrons who haven't checked out' report does not consider patrons from issues. If you have a newly registered user, that has checked out his first books in the library his borrowernumber will only appear in issues. This is an active user. At the moment Koha gets a list of active borrowernumbers only from old_issues and compares that (not in) to all borrowers to get the inactive borrowers. It should also get the borrowers from issues. Currently koha asks: SELECT CONCAT( borrowers.surname , "\t",borrowers.firstname, "\t", borrowers.cardnumber) FROM borrowers WHERE 1 AND borrowers.borrowernumber not in (SELECT DISTINCT borrowernumber FROM old_issues where old_issues.timestamp> 01/01/2010 ) group by borrowers.borrowernumber I think it needs to consider issues too, perhaps something like: SELECT CONCAT( borrowers.surname , "\t",borrowers.firstname, "\t", borrowers.cardnumber) FROM borrowers WHERE 1 AND borrowers.borrowernumber not in (SELECT DISTINCT borrowernumber FROM ( select borrowernumber from old_issues where old_issues.timestamp> 01/01/2010 union select borrowernumber from issues where issues.timestamp> 01/01/2010) active_b ) group by borrowers.borrowernumber There was some discussion about that on #koha too: http://stats.workbuffer.org/irclog/koha/2010-12-20#i_567711
Created attachment 2892 [details] screenshot of my report settings
Still fighting with this report: old_issues.timestamp> 01/01/2010 is not right and brings up a lot of 2009-* dates. :( Should be: old_issues.timestamp> "2010-01-01"
I was looking at an older version of this script earlier today, but didn't realize. I still see some problems: - the date is not showing up in the query - the issues table is still not considered SELECT CONCAT( borrowers.surname , "\t",borrowers.firstname, "\t", borrowers.cardnumber) FROM borrowers WHERE 1 AND borrowers.borrowernumber not in (SELECT DISTINCT borrowernumber FROM old_issues WHERE borrowernumber IS NOT NULL ) group by borrowers.borrowernumber
Created attachment 2897 [details] [review] Proposed patch
This bug is mentioned in: Bug 5527: Fix some problems in inactive borrowers report http://lists.koha-community.org/pipermail/koha-patches/2010-December/013449.html
Pushed, please test and mark resolved
This bug is mentioned in: Bug 5527: Fix some problems in inactive borrowers report http://lists.koha-community.org/pipermail/koha-patches/2011-March/014004.html