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

Wed Oct 10 16:09:14 EDT 2018

I know our does not but as you said the admin might be willing to run it
for us!

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

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

> You're welcome! I should mention that some consortia don't allow users
> direct access to the database for security purposes (we don't). If that's
> the case, perhaps your sys admin would be willing to run the occasional
> query for you. It doesn't hurt to ask!
>
> On Wed, Oct 10, 2018 at 3:53 PM Jennifer Bruch <jbruch at bapl.org> wrote:
>
>> 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 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
>>>
>>
>>
>
> --
> 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/21cdcbfd/attachment.html>