From c9bcd894df7d19ded99591d8d0f2362df5f8865d Mon Sep 17 00:00:00 2001 From: miker Date: Thu, 11 Dec 2008 15:54:10 +0000 Subject: [PATCH] add OCLC "bibs with new/deleted holdings" reporting source git-svn-id: svn://svn.open-ils.org/ILS/trunk@11509 dcc99617-32d9-48b4-a31d-7c20da2025e4 --- Open-ILS/src/sql/Pg/extend-reporter.sql | 25 ++++++++++++++++++++++++- 1 file changed, 24 insertions(+), 1 deletion(-) diff --git a/Open-ILS/src/sql/Pg/extend-reporter.sql b/Open-ILS/src/sql/Pg/extend-reporter.sql index 461e6e4089..1f8eaf8e24 100644 --- a/Open-ILS/src/sql/Pg/extend-reporter.sql +++ b/Open-ILS/src/sql/Pg/extend-reporter.sql @@ -25,12 +25,35 @@ CREATE TABLE extend_reporter.legacy_circ_count ( circ_count INT NOT NULL DEFAULT 0 ); -CREATE VIEW extend_reporter.full_circ_count AS +CREATE OR REPLACE VIEW extend_reporter.full_circ_count AS SELECT cp.id, COALESCE(sum(c.circ_count), 0::bigint) + COALESCE(count(circ.id), 0::bigint) AS circ_count FROM asset."copy" cp LEFT JOIN extend_reporter.legacy_circ_count c USING (id) LEFT JOIN "action".all_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