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

[vanda koha]

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 at 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 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=T
> d4SAy1bw 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
>
> _______________________________________________
> Koha mailing list  http://koha-community.org
> Koha at lists.katipo.co.nz
> https://lists.katipo.co.nz/mailman/listinfo/koha
>