Report "copy/hold ratio at pickup library and descendants" - MetaHolds Display

[Josh Stompro]

EG 2.10.6

I was attempting to use the new data source "hold/copy ratio at pickup library and descendants" to generate a set of purchase alert reports for our two systems that share our Evergreen install.

I think I found a case where this report doesn't quite work. We have a cataloging OU which is the owning location for our "on order" copies. No holds are picked up at this cataloging location. Because no holds are set to pickup at that location, it seems as if copies at that OU get excluded from this report when I try to run it. I think this is because it is gathering a list of hold/copy counts/ratios for all the pickup locations and then summarizing them. If one of the child OU's isn't mentioned in an action.hold_request then it's copy count isn't included.

This could also happen if there just didn't happen to be any holds at a certain pickup location that did have holdable copies. This could cause some instability in the results, as soon as a location filled all of it's holds, it's copies would no longer be considered as holdable copies and would drop out of the report.

Maybe this was never meant to be used at the system level, and I'm just trying to use it in a way it wasn't intended.

To test this out I picked out one bib from the results that was showing no copies for "copy_count_at_or_below" and limited the results to that copy. As soon as I added one hold with a pickup location of the OU that was holding all the on-order copies, they showed up in the "copy-count_at_or_below" count.

The original feature is at:
https://bugs.launchpad.net/evergreen/+bug/1516022

Josh

[Josh Stompro (u-launchpad-stompro-org)]

I think that this report has a bug related to meta holds also. If there is a meta hold on a bib_record, it will give a holdable copy count that includes copies from other bibs that can fulfill the hold.

I think this is because the Action.hold_copy_map will include copies for multiple bibs in the case of meta holds, and the holdable copy count is gathered by counting the distinct copies in the hold copy map. If all the holds were meta holds I can see this making sense, but in our case it is usually only one or two out of 30 that are Meta, so this really skews the ratio for all the others.

It looks like a meta hold will only have one entry in reporter.hold_request_record? So the hold will only get associated with that master_record.

I'm trying to imagine how meta holds could fit into a purchase alert report. What if a meta hold wasn't counted as a full hold, but as a fraction of a hold depending on how many of the total holdable copies was on that bib. So if Bib A had 20 holdable copies, and Bib B had 10 holdable copies, a meta hold on both bibs would count as .66 holds for Bib A and .33 for Bib B?

Anyway, to fix this issue I think we should exclude copies that are not on the rhrr bib. That way the primary metabib record gets the credit for the hold.

Maybe something like this for the with clause:
      SELECT bib_record, pickup_lib, count(DISTINCT ahr.id) AS holds_at_pickup_library,
                    COALESCE(count(DISTINCT ac.id),0) as copy_count_at_pickup_library
                FROM action.hold_request ahr
                JOIN reporter.hold_request_record rhrr USING (id)
                LEFT JOIN action.hold_copy_map ahcm ON (ahr.id = ahcm.hold)
                LEFT JOIN asset.copy ac ON (ahcm.target_copy = ac.id AND ahr.pickup_lib = ac.circ_lib)
                LEFT JOIN asset.call_number acn on (ac.call_number=acn.id)
                WHERE ahr.cancel_time IS NULL
                AND ahr.fulfillment_time IS NULL
                AND (acn.record=bib_record or ac.id is null)
                and bib_record=214607
                GROUP BY bib_record, pickup_lib

and for the everywhere copy count:
(SELECT bib_record AS id, count(DISTINCT ahr.id) AS holds_everywhere, COALESCE(count(DISTINCT target_copy),0) as copy_count_everywhere
                    FROM
                        action.hold_request ahr
                        JOIN reporter.hold_request_record rhrr USING (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)
                    WHERE
                        ahr.cancel_time IS NULL
                        AND ahr.fulfillment_time IS NULL
                        AND acn.record=bib_record
                    GROUP BY bib_record
                )y

Josh