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

Wed Oct 10 15:57:21 EDT 2018

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

-- 
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://libmail.georgialibraries.org/pipermail/open-ils-general/attachments/20181010/34b4d47f/attachment-0001.html>