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