Reporter source that excludes holds on titles a given library has on order

Bug #1797441 reported by Jessica Woolford on 2018-10-11
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Evergreen
Undecided
Unassigned

Bug Description

We have a request from a library who would like to be able to filter out holds for titles they have on order from hold reports, particularly holds that were placed a month ago or more. This is not currently possible to achieve in the reporter. Based on discussion on the Evergreen listserv, it seems like this is something that could benefit other users in the community.

For a specific look at what we're hoping to achieve, below is the SQL query we are currently using to generate the report. We're looking to change it to get bib info on all the holds, but this should get the point across.

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
left JOIN action.hold_copy_map ahcm ON ahr.id = ahcm.hold
left JOIN asset.copy ac ON ahcm.target_copy = ac.id
left JOIN asset.call_number acn ON ac.call_number = acn.id
left 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*));

To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers