Finding and deleting inactive borrowers

Finding inactive borrowers (before autumn 2023 change in the lastseen-tracking)

Allaoleva SQL  Hakee asiakkaat,

• joiden tapahtumia ei ole tilastotaulussa 15.10.2020 jälkeen,
• jotka ovat kategoriaa KOT, ERI tai LUK (=tässä tapauksessa henkilöasiakkaiden kategoriat)
• joilla ei ole lainoja
• joilla ei ole varauksia
• joilla ei ole maksuja
• joiden kortti on tehty ennen 15.10.2020
• jotka on nähty viimeksi ennen 15.10.2020 (lastseen-kenttä)
• joiden korttia on viimeksi päivitetty ennen 15.10.2020
• joiden kortti ei ole lainakiellossa

Kysely järjestää tulokset borrowernumberin (=asiakas-id), viimeksi nähdyn pvm ja päivityspvm mukaan. 

SELECT DISTINCT borrowers.borrowernumber, borrowers.surname, borrowers.firstname, borrowers.cardnumber, borrowers.categorycode, borrowers.dateenrolled, borrowers.lastseen, borrowers.updated_on, borrowers.debarred

FROM borrowers

WHERE NOT EXISTS (SELECT borrowernumber FROM statistics WHERE borrowers.borrowernumber = borrowernumber AND statistics.datetime >= '2020-10-15')

and categorycode in ('KOT', 'ERI', 'LUK')

and borrowernumber not in

  ( select borrowernumber from issues )

and borrowernumber not in

    (select borrowernumber from reserves)

and borrowernumber not in

  ( select borrowernumber

    from accountlines

    where amountoutstanding > 0 )

and dateenrolled < '2020-10-15'

and lastseen < '2020-10-15'

and updated_on < '2020-10-15'

and debarred IS NULL

order by borrowernumber, lastseen, updated_on

Deleting process:

1. Add new patron category for the patrons you wish to delete, for example POI = Poistettavat asiakkaat
2. Find the inactive borrowers you want to delete. You can use the SQL above, or something that suits your database better. Remember to change the dates and the categorycodes for borrowers!
3. Take the resulting list to Batch patron modification tool (you can do this straight from the results of the SQL report). Make sure to take all the results, not just 20 of the first ones / the first page of results!
   1. Change all the patrons to category POI = Poistettavat asiakkaat. Make sure you change all, not just the first page!
4. Go to Batch patron deletion and anonymization tool.
   1. Delete all borrowers with category POI.
   2. You can choose to Permanently delete these patrons if you wish (or move them to the trash, in which case make sure they will be deleted permanently by the cleanup_database script. )

Notes:

This SQL was made before Koha changed so that the borrowers lastseen-field will be updated on more cases. But it will be needed some time, because the change in the settings for lastseen will only trigger the lastseen-value after that day. All interactions before that have been traced or not traced based on the old settings. Also, you may want to see, why borrowers are debarred. Is there something that blocks the deletion of the patron or not? This SQL collects the "sure" cases, leaving the debarred borrowers into the database. You may find them with a separate query and decide what to do with them. The SQL is not taking into account all the possible variations of human behavior etc. in the Library. So feel free to change the SQL as you see fit for your database and library.