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 <jwoolf...@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 <jbr...@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 <jwoolf...@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 <csh...@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 <jwoolf...@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 >
