[Koha] SQL report for holds waiting for pickup longer than the ReservesMaxPickupDelay number of days

Tobin, Jessica L jtobin at fredhutch.org
Thu Mar 2 05:53:32 NZDT 2017


Hi Vanda,
     We run this report daily to keep up with holds that are languishing on the pick-up shelves for too long:


SELECT biblio.title AS 'title', p.surname, p.firstname AS 'first name', p.email, p.cardnumber AS 'patron barcode', h.waitingdate AS 'hold available date', i.barcode AS 'item barcode'
FROM reserves h
LEFT JOIN borrowers p USING (borrowernumber)
LEFT JOIN items i ON (h.biblionumber=i.biblionumber)
LEFT JOIN biblio ON (i.biblionumber=biblio.biblionumber)
WHERE date (h.waitingdate) = DATE_SUB(CURRENT_DATE(),INTERVAL 7 DAY) OR date (h.waitingdate) = DATE_SUB(CURRENT_DATE(),INTERVAL 14 DAY)
ORDER BY h.waitingdate


It shows the date that the hold became available. Also, it gives us a heads-up on holds that have been available for either 7 or 14 days, but you could remove the OR statement to just show 7 day-old holds.

Hope this helps! :)
Jess

Jessica L. Tobin
Technical Services & Systems Librarian
Arnold Library
Office 206.667.6846
Library Main 206.667.4314
jtobin at fredhutch.org

Fred Hutchinson Cancer Research Center
1100 Fairview Ave. N., Mail Stop B1-010
Seattle, WA 98109

FRED HUTCH / CURES START HERE
fredhutch.org<http://www.fredhutch.org/>




Date: Wed, 1 Mar 2017 10:16:06 +0000

From: vanda koha <vandakoha at gmail.com<mailto:vandakoha at gmail.com>>

To: koha at lists.katipo.co.nz<mailto:koha at lists.katipo.co.nz>

Subject: [Koha] SQL report for holds waiting for pickup longer than

                the ReservesMaxPickupDelay number of days

Message-ID:

                <CAOTDiOPtTBs5EXuS7o7cqQp1ioZO_Zv9+ds0eC=N=Td4SAy1bw at mail.gmail.com<mailto:CAOTDiOPtTBs5EXuS7o7cqQp1ioZO_Zv9+ds0eC=N=Td4SAy1bw at mail.gmail.com>>

Content-Type: text/plain; charset=UTF-8



Hi everybody,



I would like to run a report that would show me all the users, their e-mail adresses, titles and barcodes of items whose holds  have been waiting for pickup longer than the ReservesMaxPickupDelay number of days (in our case 7 days). Can anyone please help me with the SQL Report? It would be great if the report showed the number of days or the hold expiration date.



Many thanks,



Vanda



More information about the Koha mailing list