From 0de0e4bbf14840e794474d45213bedb521867d2e Mon Sep 17 00:00:00 2001 From: miker Date: Thu, 11 Dec 2008 15:55:27 +0000 Subject: [PATCH] add OCLC "bibs with new/deleted holdings" reporting source git-svn-id: svn://svn.open-ils.org/ILS/branches/rel_1_2_4@11512 dcc99617-32d9-48b4-a31d-7c20da2025e4 --- Open-ILS/src/sql/Pg/extend-reporter.sql | 23 +++++++++++++++++++++++ 1 file changed, 23 insertions(+) diff --git a/Open-ILS/src/sql/Pg/extend-reporter.sql b/Open-ILS/src/sql/Pg/extend-reporter.sql index 24925b7edb..e2b2ed0447 100644 --- a/Open-ILS/src/sql/Pg/extend-reporter.sql +++ b/Open-ILS/src/sql/Pg/extend-reporter.sql @@ -32,5 +32,28 @@ CREATE VIEW extend_reporter.full_circ_count AS LEFT JOIN "action".circulation circ ON circ.target_copy = cp.id GROUP BY cp.id; +CREATE OR REPLACE VIEW extend_reporter.global_bibs_by_holding_update AS + SELECT id, LAST(holding_update) AS holding_update, update_type + FROM (SELECT b.id, + LAST(cp.create_date) AS holding_update, + 'add' AS update_type + FROM biblio.record_entry b + JOIN asset.call_number cn ON (cn.record = b.id) + JOIN asset.copy cp ON (cp.call_number = cn.id) + WHERE NOT cp.deleted + AND b.id > 0 + GROUP BY b.id + UNION + SELECT b.id, + LAST(cp.edit_date) AS holding_update, + 'delete' AS update_type + FROM biblio.record_entry b + JOIN asset.call_number cn ON (cn.record = b.id) + JOIN asset.copy cp ON (cp.call_number = cn.id) + WHERE cp.deleted + AND b.id > 0 + GROUP BY b.id)x + GROUP BY id, update_type; + COMMIT; -- 2.11.0