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

[Jessica Woolford]

Thanks, Michele!

Chris, good idea. I'll file a bug ticket. I'll probably file another one
for a source that includes a way to show the format of the record that the
hold is on in a more meaningful way. I get that request a lot, and I have
just been throwing Item Form in there and having the libraries look up the
codes. Some are OK with that, but some are dissatisfied.

On Thu, Oct 11, 2018 at 9:37 AM Morgan, Michele <mmorgan at noblenet.org>
wrote:

> 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
>>
>

-- 
Jessica Woolford
Evergreen Systems Manager
Bibliomation, Inc.
24 Wooster Ave.
Waterbury, CT 06708
203-577-4070 ext. 105
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://list.evergreen-ils.org/pipermail/evergreen-reports/attachments/20181011/3c98c866/attachment.html>