wiki:reportCatalogueCount2016

Report on actively used bib records by library, 2016

Running the following query counts the number of distinct bibliographic records that are associated with:

  • 1 or more copies owned by the target library system
  • 1 or more URIs (URLs for electronic resources) owned by the target library system

The report filters out deleted bib records and deleted call numbers and avoids duplicates. It is my belief that this is an accurate reflection of the number of bib records a given library would need to extract if it wanted to leave Conifer and create its own library system.

Note: If one wants to consider the number of records required for a single system (for example, all of the records required by the Laurentian campus libraries), it may be lower than the simple sum of all of the individual libraries in the system. For example, two different libraries in the same system may have attached copies to the same record.

Note: This does not count the number of records required for URIs, as the required location for those changed so that they are recorded at the system level rather than at the individual library level.

As of April 7, 2016, here's the list of libraries and the number of bib records each one currently requires:

conifer=# CREATE FUNCTION conifer.count_bibs(INTEGER) RETURNS BIGINT AS $$
  SELECT COUNT(*) FROM (
    SELECT DISTINCT record
    FROM asset.call_number acn
      INNER JOIN biblio.record_entry bre ON bre.id = acn.record
    WHERE acn.deleted IS FALSE
      AND bre.deleted IS FALSE
      AND (owning_lib IN (SELECT id FROM actor.org_unit WHERE parent_ou = $1)
        OR owning_lib = $1
      )
    ) AS x;
$$ LANGUAGE SQL;

WITH raw AS (
  SELECT id, conifer.count_bibs(id)
  FROM actor.org_unit
  WHERE (parent_ou = 1 OR ou_type IN (3,4))
    AND opac_visible IS TRUE
)
SELECT aoup.name AS "Parent", aou.name, raw.count_bibs
  FROM raw
    INNER JOIN actor.org_unit aou ON aou.id = raw.id
    LEFT JOIN actor.org_unit aoup ON aoup.id = aou.parent_ou
  ORDER BY 1, 3 DESC;

                   Parent                   |                      name                       | count_bibs 
--------------------------------------------+-------------------------------------------------+------------
 Algoma System                              | Algoma University, Wishart Library              |      89134
 Algoma System                              | Lest We Forget Remembrance and Military Library |       1219
 Child and Community Resources              | CCR - Sudbury                                   |        690
 Child and Community Resources              | CCR - Thunder Bay                               |        323
 Child and Community Resources              | CCR - Sault. Ste. Marie                         |          1
 Child and Community Resources              | CCR - Fort Frances                              |          0
 Collège Boréal                             | Collège Boréal - Sudbury                        |      19919
 Collège Boréal                             | Collège Boréal - Timmins                        |       1501
 Collège Boréal                             | Collège Boréal - Toronto                        |       1122
 Collège Boréal                             | Collège Boréal - Témiskaming                    |        607
 Collège Boréal                             | Collège Boréal - Kapuskasing                    |        257
 Collège Boréal                             | Collège Boréal - Hearst                         |         97
 Collège Boréal                             | Collège Boréal - Nipissing                      |          6
 Conifer                                    | Laurentian University                           |    1044160
 Conifer                                    | Algoma System                                   |     189371
 Conifer                                    | Collège Boréal                                  |      47736
 Conifer                                    | Université de Hearst                            |      37697
 Conifer                                    | Special Libraries                               |      36083
 Conifer                                    | Northern Ontario Health Library Consortium      |      23202
 Conifer                                    | Child and Community Resources                   |       1010
 Laurentian University                      | J.N. Desmarais Library                          |     438635
 Laurentian University                      | University of Sudbury                           |      25817
 Laurentian University                      | Laboratoire de didactiques, E.S.E.              |      12860
 Laurentian University                      | Huntington University Library                   |      11333
 Laurentian University                      | Music Resource Centre                           |       6443
 Laurentian University                      | School of Architecture                          |       2698
 Laurentian University                      | Instructional Media Centre                      |        856
 Northern Ontario Health Library Consortium | HSN Health Sciences Library                     |       7823
 Northern Ontario Health Library Consortium | Northern Ontario School of Medicine (East)      |       7560
 Northern Ontario Health Library Consortium | Northern Ontario School of Medicine (West)      |       6938
 Northern Ontario Health Library Consortium | St. Joseph's Care Group                         |       3080
 Northern Ontario Health Library Consortium | Sault Area Hospital                             |       2007
 Northern Ontario Health Library Consortium | North Bay Regional Health Centre                |       1781
 Special Libraries                          | Mining and the Environment Database             |      23214
 Special Libraries                          | Xstrata Process Support Centre Library          |       4553
 Special Libraries                          | Art Gallery of Sudbury                          |       2557
 Special Libraries                          | Centre Franco-Ontarien de Folklore              |       2550
 Special Libraries                          | John B. Gammon Geoscience Library               |       2377
 Special Libraries                          | Vale Inco                                       |        830
 Special Libraries                          | Northeast Mental Health Centre                  |          2
 Université de Hearst                       | Hearst, Bibliothèque Maurice-Saulnier           |      35258
 Université de Hearst                       | Hearst Timmins, Centre de Ressources            |       4422
 Université de Hearst                       | Hearst Kapuskasing, Centre de Ressources        |       2314
(43 rows)

CREATE FUNCTION conifer.count_lu_to_uofs_copies() RETURNS BIGINT AS $$
  WITH uofs_copies AS (
    SELECT id FROM asset.copy
    WHERE deleted IS FALSE
      AND circ_lib = 107
   ), lu_oldcopies AS (
    SELECT id FROM auditor.asset_copy_history
    WHERE audit_time > '2015-01-01'::date
      AND audit_time < '2016-01-01'::date
      AND circ_lib = 103
      AND deleted IS FALSE
    GROUP BY id
   )
   SELECT COUNT(*) FROM uofs_copies
     INNER JOIN lu_oldcopies ON uofs_copies.id = lu_oldcopies.id
$$ LANGUAGE SQL;

conifer=# SELECT conifer.count_lu_to_uofs_copies();
 count_lu_to_uofs_copies 
-------------------------
                     799
(1 row)
Last modified 3 years ago Last modified on Apr 7, 2016, 11:04:36 AM