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;