Records Count and Created in Last Year

Building off of Jeremy’s records count query that he posted in the Discord, I created (with the help of ChatGPT) a query that will also look at the count of records created between dates (in this case, 1/1/2024 - 12/31/2024). This will potentially help predict when you’ll run out of various records and can be run every year to monitor the status of the various record types.

WITH bibs AS (
  SELECT 
    r.accounting_unit_code_num,
    r.record_type_code,
    r.start_num,
    r.last,
    r.current_count,
    r.deleted_count,
    r.max_num,
    r.size,
    r.size - (r.last - r.start_num) AS est_remaining_count,
    COUNT(bv.record_num) FILTER (
      WHERE bv.record_creation_date_gmt BETWEEN '2024-01-01' AND '2024-12-31'
    ) AS created_in_2024
  FROM sierra_view.record_range r
  LEFT JOIN sierra_view.bib_view bv 
    ON r.start_num <= bv.record_num AND bv.record_num <= r.last
  WHERE r.record_type_code = 'b'
  GROUP BY r.accounting_unit_code_num, r.record_type_code, r.start_num, r.last, 
           r.current_count, r.deleted_count, r.max_num, r.size
),
items AS (
  SELECT 
    r.accounting_unit_code_num,
    r.record_type_code,
    r.start_num,
    r.last,
    r.current_count,
    r.deleted_count,
    r.max_num,
    r.size,
    r.size - (r.last - r.start_num) AS est_remaining_count,
    COUNT(iv.record_num) FILTER (
      WHERE iv.record_creation_date_gmt BETWEEN '2024-01-01' AND '2024-12-31'
    ) AS created_in_2024
  FROM sierra_view.record_range r
  LEFT JOIN sierra_view.item_view iv 
    ON r.start_num <= iv.record_num AND iv.record_num <= r.last
  WHERE r.record_type_code = 'i'
  GROUP BY r.accounting_unit_code_num, r.record_type_code, r.start_num, r.last, 
           r.current_count, r.deleted_count, r.max_num, r.size
),
orders AS (
  SELECT 
    r.accounting_unit_code_num,
    r.record_type_code,
    r.start_num,
    r.last,
    r.current_count,
    r.deleted_count,
    r.max_num,
    r.size,
    r.size - (r.last - r.start_num) AS est_remaining_count,
    COUNT(ov.record_num) FILTER (
      WHERE ov.record_creation_date_gmt BETWEEN '2024-01-01' AND '2024-12-31'
    ) AS created_in_2024
  FROM sierra_view.record_range r
  LEFT JOIN sierra_view.order_view ov 
    ON r.start_num <= ov.record_num AND ov.record_num <= r.last
  WHERE r.record_type_code = 'o'
  GROUP BY r.accounting_unit_code_num, r.record_type_code, r.start_num, r.last, 
           r.current_count, r.deleted_count, r.max_num, r.size
),
patrons AS (
  SELECT 
    r.accounting_unit_code_num,
    r.record_type_code,
    r.start_num,
    r.last,
    r.current_count,
    r.deleted_count,
    r.max_num,
    r.size,
    r.size - (r.last - r.start_num) AS est_remaining_count,
    COUNT(prm.record_num) FILTER (
      WHERE prm.creation_date_gmt BETWEEN '2024-01-01' AND '2024-12-31'
    ) AS created_in_2024
  FROM sierra_view.record_range r
  LEFT JOIN sierra_view.record_metadata prm 
    ON r.start_num <= prm.record_num AND prm.record_num <= r.last
       AND prm.record_type_code = 'p'
  WHERE r.record_type_code = 'p'
  GROUP BY r.accounting_unit_code_num, r.record_type_code, r.start_num, r.last, 
           r.current_count, r.deleted_count, r.max_num, r.size
),
holdings AS (
  SELECT 
    r.accounting_unit_code_num,
    r.record_type_code,
    r.start_num,
    r.last,
    r.current_count,
    r.deleted_count,
    r.max_num,
    r.size,
    r.size - (r.last - r.start_num) AS est_remaining_count,
    COUNT(hv.record_num) FILTER (
      WHERE hv.record_creation_date_gmt BETWEEN '2024-01-01' AND '2024-12-31'
    ) AS created_in_2024
  FROM sierra_view.record_range r
  LEFT JOIN sierra_view.holding_view hv 
    ON r.start_num <= hv.record_num AND hv.record_num <= r.last
  WHERE r.record_type_code = 'c'
  GROUP BY r.accounting_unit_code_num, r.record_type_code, r.start_num, r.last, 
           r.current_count, r.deleted_count, r.max_num, r.size
),
vendors AS (
  SELECT 
    r.accounting_unit_code_num,
    r.record_type_code,
    r.start_num,
    r.last,
    r.current_count,
    r.deleted_count,
    r.max_num,
    r.size,
    r.size - (r.last - r.start_num) AS est_remaining_count,
    COUNT(vv.record_num) FILTER (
      WHERE vv.record_creation_date_gmt BETWEEN '2024-01-01' AND '2024-12-31'
    ) AS created_in_2024
  FROM sierra_view.record_range r
  LEFT JOIN sierra_view.vendor_view vv 
    ON r.start_num <= vv.record_num AND vv.record_num <= r.last
  WHERE r.record_type_code = 'v'
  GROUP BY r.accounting_unit_code_num, r.record_type_code, r.start_num, r.last, 
           r.current_count, r.deleted_count, r.max_num, r.size
),
authorities AS (
  SELECT 
    r.accounting_unit_code_num,
    r.record_type_code,
    r.start_num,
    r.last,
    r.current_count,
    r.deleted_count,
    r.max_num,
    r.size,
    r.size - (r.last - r.start_num) AS est_remaining_count,
    COUNT(av.record_num) FILTER (
      WHERE av.record_creation_date_gmt BETWEEN '2024-01-01' AND '2024-12-31'
    ) AS created_in_2024
  FROM sierra_view.record_range r
  LEFT JOIN sierra_view.authority_view av 
    ON r.start_num <= av.record_num AND av.record_num <= r.last
  WHERE r.record_type_code = 'a'
  GROUP BY r.accounting_unit_code_num, r.record_type_code, r.start_num, r.last, 
           r.current_count, r.deleted_count, r.max_num, r.size
),
invoices AS (
  SELECT 
    r.accounting_unit_code_num,
    r.record_type_code,
    r.start_num,
    r.last,
    r.current_count,
    r.deleted_count,
    r.max_num,
    r.size,
    r.size - (r.last - r.start_num) AS est_remaining_count,
    COUNT(inv.record_num) FILTER (
      WHERE inv.record_creation_date_gmt BETWEEN '2024-01-01' AND '2024-12-31'
    ) AS created_in_2024
  FROM sierra_view.record_range r
  LEFT JOIN sierra_view.invoice_view inv 
    ON r.start_num <= inv.record_num AND inv.record_num <= r.last
  WHERE r.record_type_code = 'n'
  GROUP BY r.accounting_unit_code_num, r.record_type_code, r.start_num, r.last, 
           r.current_count, r.deleted_count, r.max_num, r.size
)
-- Combine results
SELECT * FROM bibs
UNION ALL
SELECT * FROM items
UNION ALL
SELECT * FROM orders
UNION ALL
SELECT * FROM patrons
UNION ALL
SELECT * FROM holdings
UNION ALL
SELECT * FROM vendors
UNION ALL
SELECT * FROM authorities
UNION ALL
SELECT * FROM invoices
ORDER BY 1, 2;
2 Likes

Interesting query @bethany, and thanks for sharing!  Since the goal of this query is to monitor record consumption though, I think it would be best to steer clear of the record-name_view views when calculating your “Created Last Year” column and instead draw from record_metadata, as that will also include records created last year and since deleted.

If you just want to easily monitor the status of the various record types, check INNOVATIVE SYSTEM STATUS (M > I > F > S). It shows the number of records your system has now as well as the maximum number of records currently allowed, per record type (RECORD TYPE; LAST REC#; CURRENT#; MAX ALLOWED; #DELETED). You can request an increase in the number of records of any record type that is getting low.

1 Like