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

[Jessica Woolford]

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*));

[Jeff Davis (jdavis-sitka)]

Bug 1845238 may help with this -- it adds a link from holds to the hold copy map, which appears to be required to implement your SQL query via the reporter.

[Jessica Woolford (jwoolford)]

Thanks, Jeff, I'll take a look at your branch.