From 4c9f565570b8b8b3f3b8ad7d31a878afa498638c Mon Sep 17 00:00:00 2001 From: Galen Charlton Date: Mon, 19 Oct 2015 20:41:12 +0000 Subject: [PATCH] LP#1516022: new reporting source for hold/copy ratios This patch adds a new reporting source, "Hold/Copy Ratio per Bib and Pickup Library (and Descendants)", that, for each bib that has a hold request on it or any of its components, calculates the following: - active holds at each OU (including the OU's descendants) - holdable copies at each OU (and its descendants) - the ratio of the above two counts - counts and ratio across the entire consortium This work was sponsored by the Pennsylvania Integrated Library System (PaILS). Signed-off-by: Galen Charlton Signed-off-by: Josh Stompro Signed-off-by: Mike Rylander --- Open-ILS/examples/fm_IDL.xml | 56 ++++++++++++++++++++++++++++++++++++ 1 file changed, 56 insertions(+) diff --git a/Open-ILS/examples/fm_IDL.xml b/Open-ILS/examples/fm_IDL.xml index 8e8df0e248..4a5626901b 100644 --- a/Open-ILS/examples/fm_IDL.xml +++ b/Open-ILS/examples/fm_IDL.xml @@ -10574,6 +10574,62 @@ SELECT usr, + + + WITH counts_at_ou AS ( + 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) + WHERE ahr.cancel_time IS NULL + AND ahr.fulfillment_time IS NULL + GROUP BY bib_record, pickup_lib + ) + SELECT *, + CASE WHEN copy_count_at_or_below = 0 THEN 'Infinity'::FLOAT ELSE holds_at_or_below::FLOAT/copy_count_at_or_below END AS hold_copy_ratio_at_or_below_ou, + CASE WHEN copy_count_everywhere = 0 THEN 'Infinity'::FLOAT ELSE holds_everywhere::FLOAT/copy_count_everywhere END AS everywhere_ratio + FROM + (SELECT bib_record AS id, aou.id AS pickup_lib_or_desc, SUM(holds_at_pickup_library) AS holds_at_or_below, SUM(copy_count_at_pickup_library) AS copy_count_at_or_below + FROM actor.org_unit aou + JOIN counts_at_ou cao ON (cao.pickup_lib IN (SELECT id FROM actor.org_unit_descendants(aou.id))) + GROUP BY bib_record, pickup_lib_or_desc + )x + JOIN + (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) + WHERE + ahr.cancel_time IS NULL + AND ahr.fulfillment_time IS NULL + GROUP BY bib_record + )y + USING (id) + + + + + + + + + + + + + + + + + + + + + + SELECT -- 2.43.2