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

[Jessica Woolford]

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
> Bethlehem, PA 18018
> 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.
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/71709fd3/attachment-0001.html>