Open-ILS/src/sql/Pg/upgrade/1060.LP1582354_report_able_to_show_bibs_where_the_last_copy_was_deleted_cancels.sql

   1 BEGIN;
   2
   3 SELECT evergreen.upgrade_deps_block_check('1060', :eg_version);
   4
   5 DROP VIEW IF EXISTS extend_reporter.copy_count_per_org;
   6
   7
   8 CREATE OR REPLACE VIEW extend_reporter.copy_count_per_org AS
   9  SELECT acn.record AS bibid,
  10     ac.circ_lib,
  11     acn.owning_lib,
  12     max(ac.edit_date) AS last_edit_time,
  13     min(ac.deleted::integer) AS has_only_deleted_copies,
  14     count(
  15         CASE
  16             WHEN ac.deleted THEN ac.id
  17             ELSE NULL::bigint
  18         END) AS deleted_count,
  19     count(
  20         CASE
  21             WHEN NOT ac.deleted THEN ac.id
  22             ELSE NULL::bigint
  23         END) AS visible_count,
  24     count(*) AS total_count
  25    FROM asset.call_number acn,
  26     asset.copy ac
  27   WHERE ac.call_number = acn.id
  28   GROUP BY acn.record, acn.owning_lib, ac.circ_lib;
  29
  30
  31 COMMIT;