[OPEN-ILS-GENERAL] Filtering acquisitions items out of a hold report

[Morgan, Michele]

I think a holds reporter source would be useful to many Evergreen users.

Also, a suggestion for Jessica's query - adding left joins will pick up
holds that don't appear in the hold_copy_map:

SELECT DISTINCT ON (ahr.id) ahr.request_time, acard.barcode,
au.family_name, au.first_given_name, rmsr.title, rmsr.tcn_value
FROM action.hold_request ahr
JOIN actor.usr au ON ahr.usr = au.id
JOIN actor.card acard ON au.card = acard.id
left JOIN action.hold_copy_map ahcm ON ahr.id = ahcm.hold
left JOIN asset.copy ac ON ahcm.target_copy = ac.id
left JOIN asset.call_number acn ON ac.call_number = acn.id
left JOIN reporter.materialized_simple_record rmsr ON rmsr.id = acn.record
WHERE ahr.pickup_lib = *org_unit id*
AND ahr.capture_time IS NULL
AND ahr.cancel_time IS NULL
AND ahr.fulfillment_time IS NULL
AND ahr.frozen = FALSE
AND date(ahr.request_time) < '2018-09-01'
AND ahr.id NOT IN (SELECT hold FROM action.hold_copy_map WHERE target_copy
IN (SELECT id FROM asset.copy WHERE status = 9 AND circ_lib = *org_unit
id*));

Hope this helps,
Michele

--
Michele M. Morgan, Technical Support Analyst
North of Boston Library Exchange, Danvers Massachusetts
mmorgan at noblenet.org



On Thu, Oct 11, 2018 at 6:41 AM Chris Sharp <csharp at georgialibraries.org>
wrote:

> Thanks for your question. It made me go back and look at the generated
>> SQL, and realize that what they are looking for is probably too complex for
>> the reporter. I ended up writing my own SQL query to generate the report.
>> I'm happy to share it if anyone is interested.
>>
>
> If this is something libraries want/need, that makes a case for creating a
> new reports source.  I would suggest opening a wishlist bug report if you
> think that's the case.  Perhaps we could take a stab at doing this at the
> Hack-A-Way next month?
>
> --
> Chris Sharp
> PINES System Administrator
> Georgia Public Library Service
> 1800 Century Place, Suite 580
> Atlanta, Georgia 30345
> (404) 235-7147
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://libmail.georgialibraries.org/pipermail/open-ils-general/attachments/20181011/4eb1f2ba/attachment.html>