Re: [Koha] SQL report for holds waiting for pickup longer than the ReservesMaxPickupDelay number of days
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@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@gmail.com<mailto:vandakoha@gmail.com>> To: koha@lists.katipo.co.nz<mailto:koha@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@mail.gmail.com<mailto:CAOTDiOPtTBs5EXuS7o7cqQp1ioZO_Zv9+ds0eC=N=Td4SAy1bw@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
Jessica and Liz, Thanks a lot :) That's exactly what I was looking for. It was a great help! Regards, Vanda 2017-03-01 16:53 GMT+00:00 Tobin, Jessica L <jtobin@fredhutch.org>:
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@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@gmail.com<mailto:vandakoha@gmail.com>>
To: koha@lists.katipo.co.nz<mailto:koha@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@mail. gmail.com<mailto:CAOTDiOPtTBs5EXuS7o7cqQp1ioZO_Zv9+ds0eC=N=T d4SAy1bw@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
_______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz https://lists.katipo.co.nz/mailman/listinfo/koha
participants (2)
-
Tobin, Jessica L -
vanda koha