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

Wed Oct 10 15:52:10 EDT 2018

Thanks

*Jennifer Bruch*
Bethlehem Area Public Library
11 W. Church Street
Bethlehem, PA 18018
610-867-3761 x232

On Wed, Oct 10, 2018 at 3:47 PM, Jessica Woolford <jwoolford at biblio.org>
wrote:

> Hi Jennifer,
>
> First, check with your system administrator to make sure you have access
> to your Evergreen database, either through psql (command line) or a GUI
> (PGAdmin).
>
> Then, I would recommend watching Rogan Hamby's excellent presentation from
> the 2014 Evergreen conference - SQL for Librarians:
> https://youtu.be/3Iz-HFiDq6E
>
> Below is the query I used. It took me a long time and a lot of practice to
> learn how to write something like this, so don't worry if you struggle at
> first. I probably have some bad habits and certainly don't know all there
> is to know. For instance, if I wanted to be really proper, I would define
> the type of join I'm using. Default is an inner join, which means only
> values that have matching values in both tables will appear in the output
> (think the part where the two circles intersect in a ven diagram).
>
> 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
> JOIN action.hold_copy_map ahcm ON ahr.id = ahcm.hold
> JOIN asset.copy ac ON ahcm.target_copy = ac.id
> JOIN asset.call_number acn ON ac.call_number = acn.id
> 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*));
>
> I hope that helps!
> Jessica
>
> On Wed, Oct 10, 2018 at 3:12 PM Jennifer Bruch <jbruch at bapl.org> wrote:
>
>> Hi Jessica,
>> My library cataloger is interested in having a report like this as well
>> but I have no experience using SQL on the backend of Evergreen. Can you
>> tell me how you go about learning this?
>>
>> Thanks
>>
>> *Jennifer Bruch*
>> Bethlehem Area Public Library
>> 11 W. Church Street
>> <https://maps.google.com/?q=11+W.+Church+Street+%C2%A0+Bethlehem,+PA+18018&entry=gmail&source=g>
>> Bethlehem, PA 18018
>> <https://maps.google.com/?q=11+W.+Church+Street+%C2%A0+Bethlehem,+PA+18018&entry=gmail&source=g>
>>
>> 610-867-3761 x232
>>
>> On Wed, Oct 10, 2018 at 2:26 PM, Jessica Woolford <jwoolford at biblio.org>
>> wrote:
>>
>>> Hi Chris,
>>>
>>> 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.
>>>
>>> Thanks again!
>>> Jessica
>>>
>>>
>>>
>>> On Tue, Oct 2, 2018 at 6:28 AM Chris Sharp <csharp at georgialibraries.org>
>>> wrote:
>>>
>>>> Jessica,
>>>>
>>>> Can you provide the generated SQL from the attempts that aren't working?
>>>>
>>>> Thanks!
>>>>
>>>> Chris
>>>>
>>>> On Mon, Oct 1, 2018 at 2:30 PM Jessica Woolford <jwoolford at biblio.org>
>>>> wrote:
>>>>
>>>>> Hi all,
>>>>>
>>>>> We have a library who wants a report listing requests that have not
>>>>> been filled in over a month. That is no problem - we have several libraries
>>>>> using a report that can do this. However, they would also like to filter
>>>>> their acquisitions items out of the report. The only indication that an
>>>>> item is in acquisitions is on the copy level, so this has been challenging
>>>>> to achieve in the reporter.
>>>>>
>>>>> These are constant filters I have in the report:
>>>>>
>>>>> Hold Request > Request Date/Time (transform: Date, Operator: Before
>>>>> (Date/Time)
>>>>> Hold Request > Pickup Library (Operator: In List)
>>>>> Hold Request > Capture Date/Time (Operator: Is NULL)
>>>>> Hold Request > Hold Cancel Date/Time (Operator: Is NULL)
>>>>> Hold Request > Currently Frozen (Operator: Equals, Value: False)
>>>>>
>>>>> Here are the filters I have tried:
>>>>>
>>>>> Hold Request > Bib Record link > Target Bib Record > Call Number >
>>>>> Copies > Copy Status > id (Operator: In List, Value: On order (chosen by
>>>>> user))
>>>>> I still get holds on bibs that have copies with a status of On order.
>>>>>
>>>>> Hold Request > Bib Record link > Target Bib Record > Call Number >
>>>>> Copies > Active Date/Time (Operator Is NOT NULL)
>>>>> I still get holds on bibs that have copies with no Active Date.
>>>>>
>>>>> I have played a little bit with nullability selection on both fields,
>>>>> but I haven't had any success.
>>>>>
>>>>> Can anybody think of anything that I haven't considered?
>>>>>
>>>>> Thanks,
>>>>> Jessica
>>>>> --
>>>>> Jessica Woolford
>>>>> Evergreen Systems Manager
>>>>> Bibliomation, Inc.
>>>>> 24 Wooster Ave.
>>>>> <https://maps.google.com/?q=24+Wooster+Ave.+Waterbury,+CT+06708&entry=gmail&source=g>
>>>>> Waterbury, CT 06708
>>>>> <https://maps.google.com/?q=24+Wooster+Ave.+Waterbury,+CT+06708&entry=gmail&source=g>
>>>>> 203-577-4070 ext. 105
>>>>>
>>>>
>>>>
>>>> --
>>>> Chris Sharp
>>>> PINES System Administrator
>>>> Georgia Public Library Service
>>>> 1800 Century Place, Suite 580
>>>> <https://maps.google.com/?q=1800+Century+Place,+Suite+580+Atlanta,+Georgia+30345&entry=gmail&source=g>
>>>> Atlanta, Georgia 30345
>>>> <https://maps.google.com/?q=1800+Century+Place,+Suite+580+Atlanta,+Georgia+30345&entry=gmail&source=g>
>>>> (404) 235-7147
>>>>
>>>
>>>
>>> --
>>> Jessica Woolford
>>> Evergreen Systems Manager
>>> Bibliomation, Inc.
>>> 24 Wooster Ave.
>>> <https://maps.google.com/?q=24+Wooster+Ave.+Waterbury,+CT+06708&entry=gmail&source=g>
>>> Waterbury, CT 06708
>>> <https://maps.google.com/?q=24+Wooster+Ave.+Waterbury,+CT+06708&entry=gmail&source=g>
>>> 203-577-4070 ext. 105
>>>
>>
>>
>
> --
> Jessica Woolford
> Evergreen Systems Manager
> Bibliomation, Inc.
> 24 Wooster Ave.
> <https://maps.google.com/?q=24+Wooster+Ave.+Waterbury,+CT+06708&entry=gmail&source=g>
> Waterbury, CT 06708
> <https://maps.google.com/?q=24+Wooster+Ave.+Waterbury,+CT+06708&entry=gmail&source=g>
> 203-577-4070 ext. 105
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://libmail.georgialibraries.org/pipermail/open-ils-general/attachments/20181010/664f8213/attachment.html>