[Evergreen-reports] Cancelled Holds Report Template

[Willis, Beth]

Hi Lindsay,

Thank you for your informative message.  I am a little unclear of what you
meant by this sentence, though:

"For your report, are you trying to find hold requests that were cancelled
after being made available, or hold items that are still on a hold shelf
after the hold request was cancelled?"

What is the difference between being made available and remaining on the
hold shelf?

Beth

On Tue, Dec 29, 2020 at 10:59 AM Lindsay Stratton <lstratton at wlsmail.org>
wrote:

> Hi Beth,
>
> I've got several SQL queries that compare columns like your example that
> I've tried to translate into the reporter, and I have found the answer to
> be "no, but..." The only way to get around it is to figure out how to
> reframe the comparison to use an actual value in the filter, which tends to
> be either terrifically specific or less accurate. For example, I have a
> report that looks at hold transits filtered to exclude transactions where
> the hold fulfillment library matches the copy owning/circ library - in the
> reporter I would basically need a separate template for each library (ugh).
>
> For your report, are you trying to find hold requests that were cancelled
> after being made available, or hold items that are still on a hold shelf
> after the hold request was cancelled?
>
> If the former, Rogan's suggestion to compare capture/shelf time and cancel
> time, using just the hold request source would be the most direct, and also
> sort of achievable in the reporter. If you use the filters "shelf time less
> than" with a specific date (or "shelf time is not null" returns similar
> results) and "cancel time between", that will return a mostly accurate list
> of holds cancelled after they were made available. I also suggest adding
> fulfillment time is null (if you use the related items can fulfill holds
> setting). Adding the copy status info would skew your results by
> eliminating items that had subsequent holds/circulation.
>
> If the latter, filtering on copy status 8 and cancel time between (or is
> not null) should be sufficient. I'm not sure you need to match the shelf
> time to the copy status changed time.
>
> Good luck!
>
> Lindsay
>
> *Lindsay Stratton*
> *Systems Librarian*
> Westchester Library System
> 570 Taxter Rd., 4th Floor
> Elmsford, NY 10523
> lstratton at wlsmail.org
>
>
> On Mon, Dec 21, 2020 at 10:43 AM Willis, Beth <willis at noblenet.org> wrote:
>
>> Thanks, Terran.
>>
>> I should have been more specific in my initial post.   And, the query I
>> want to run might have helped (I have pasted it below).  It is the last
>> filter that is tripping me up.  Is it possible to translate that into
>> reporter-speak?
>>
>> Thanks!
>> Beth
>>
>>
>> SELECT
>>
>> ahr.shelf_time as ShelfTime,
>> ac.status_changed_time as StatusChange,
>>
>> date(ahr.cancel_time) AS HoldCancelDate,
>>
>> ahr.id as hold_id,
>> ahr.current_copy,
>> ac.barcode AS Barcode,
>> st.name,
>>
>> sr.title AS Title,
>> au.first_given_name AS FirstName,
>> au.family_name AS LastName
>>
>> FROM action.hold_request ahr
>> JOIN reporter.hold_request_record  rhr ON ahr.id = rhr.id
>> JOIN biblio.record_entry re ON rhr.bib_record = re.id
>> LEFT OUTER JOIN reporter.materialized_simple_record sr  ON re.id = sr.id
>>
>> INNER JOIN actor.usr au ON ahr.usr = au.id
>>
>> LEFT OUTER JOIN action.hold_copy_map hcm ON ahr.id = hcm.hold
>> JOIN asset.copy ac ON ahr.current_copy = ac.id
>> JOIN config.copy_status st on ac.status = st.id
>>
>> WHERE
>> date(ahr.cancel_time) BETWEEN '2020-12-01' AND '2020-12-17'
>> AND ahr.pickup_lib IN (46)
>> AND date(ahr.capture_time) is not NULL
>> and ac.status = 8
>> and ahr.shelf_time = ac.status_changed_time
>>
>> On Mon, Dec 21, 2020 at 9:49 AM Terran McCanna <
>> tmccanna at georgialibraries.org> wrote:
>>
>>> It sounds like you are looking for something like this one which we used
>>> to find holds that were captured (pulled off the shelf) but not fulfilled
>>> (not picked up):
>>>
>>> -----------------------------------------
>>> Uses Hold Request source.
>>>
>>> Displayed Fields:
>>>
>>> Hold Request -> Pickup Library -> Short (Policy) Name as "Pickup Library"
>>> Hold Request -> Hold Type
>>> Hold Request -> Request Date/Time
>>> Hold Request -> Capture Date/Time
>>> Hold Request -> Bib Record Link -> Bib Record -> Simple Record Extracts -> Title Proper (normalized) as "Title"
>>> Hold Request -> Bib Record Link -> Bib Record -> Simple Record Extracts -> Author (normalized) as "Author"
>>> Hold Request -> Hold User -> Current Library Card -> Barcode as "Patron Barcode"
>>> Hold Request -> Currently Frozen
>>> Hold Request -> Thaw Date (if frozen)
>>> Hold Request -> Hold Expire Date/Time
>>>
>>> Base Filters:
>>>
>>> Hold Request -> Pickup Library -> Organizational Unit ID (In list)
>>> Hold Request -> Capture Date/Time (Date) (Between)
>>> Hold Request -> Fulfillment Date/Time (Is NULL)
>>> Hold Request -> Request Date/Time (Date) (Between)
>>> Hold Request -> Hold Cancel Date/Time (Is NULL)
>>>
>>>
>>> Terran McCanna, PINES Program Manager
>>> ------------------------------
>>>
>>> Georgia Public Library Service | University System of Georgia
>>>
>>> 2872 Woodcock Blvd, Suite 250 l Atlanta, GA 30341
>>>
>>> (404) 235-7138 | tmccanna at georgialibraries.org
>>>
>>> http://help.georgialibraries.org | help at georgialibraries.org
>>>
>>> <https://www.facebook.com/georgialibraries>
>>> <https://www.twitter.com/georgialibs>
>>> <https://www.instagram.com/georgialibraries/>
>>> <https://www.twitter.com/georgialibs>
>>>
>>> Join our email list <http://georgialibraries.org> for stories of
>>> Georgia libraries making an impact in our communities.
>>>
>>>
>>>
>>> On Mon, Dec 21, 2020 at 9:30 AM Willis, Beth <willis at noblenet.org>
>>> wrote:
>>>
>>>> Hi everyone,
>>>>
>>>> I am trying to create a report template that includes a filter with a
>>>> default value, but where that default value references the value of a
>>>> different field.
>>>>
>>>> For example, I am working on a template to show holds that have been
>>>> cancelled after the targeted items have been put on the hold shelf.  I want
>>>> to include the action.hold_request.shelf_time as a filter and set its
>>>> default value to that of the asset.copy.status_chhnged_time.
>>>>
>>>> Does anyone know how to do this?  Is it possible in the reporter?
>>>>
>>>> Does anyone have a template for  a cancelled holds report like this?
>>>>
>>>> Thanks in advance!
>>>> Beth
>>>> --
>>>> Beth Willis
>>>> Digital & Catalog Librarian
>>>> NOBLE, Inc.
>>>> 42 Cherry Hill Drive
>>>> Danvers, MA 01923
>>>> _______________________________________________
>>>> Evergreen-reports mailing list
>>>> Evergreen-reports at list.evergreen-ils.org
>>>> http://list.evergreen-ils.org/cgi-bin/mailman/listinfo/evergreen-reports
>>>>
>>> _______________________________________________
>>> Evergreen-reports mailing list
>>> Evergreen-reports at list.evergreen-ils.org
>>> http://list.evergreen-ils.org/cgi-bin/mailman/listinfo/evergreen-reports
>>>
>>
>>
>> --
>> Beth Willis
>> Digital & Catalog Librarian
>> NOBLE, Inc.
>> 42 Cherry Hill Drive
>> Danvers, MA 01923
>> _______________________________________________
>> Evergreen-reports mailing list
>> Evergreen-reports at list.evergreen-ils.org
>> http://list.evergreen-ils.org/cgi-bin/mailman/listinfo/evergreen-reports
>>
> _______________________________________________
> Evergreen-reports mailing list
> Evergreen-reports at list.evergreen-ils.org
> http://list.evergreen-ils.org/cgi-bin/mailman/listinfo/evergreen-reports
>


-- 
Beth Willis
Digital & Catalog Librarian
NOBLE, Inc.
42 Cherry Hill Drive
Danvers, MA 01923
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://list.evergreen-ils.org/pipermail/evergreen-reports/attachments/20201229/31b64351/attachment-0001.html>