-
-
Save jeff/252fa162d30265abe92a to your computer and use it in GitHub Desktop.
Query to count "lost" copies by the profile group of the user that "lost" them.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
profile | lost_count | |
------------------------------------------+------------ | |
Users -> Patrons | 13790 | |
Users -> Patrons -> Interloan | 3 | |
Users -> Patrons -> Limited | 276 | |
Users -> Patrons -> Special-No-Fines | 3 | |
Users -> Staff -> Catalogers | 2 | |
(5 rows) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- Count copies in "Lost" or "Lost and Paid" status that have an open circulation with | |
-- stop_fines of LOST, grouped by the primary profile of that circulation's user | |
-- See comments below for additional limiters | |
-- Neat CTE to give full path of permission groups | |
-- modified from an example by Dan Scott | |
WITH RECURSIVE profile_tree(id, parent, name) AS ( | |
SELECT id, parent, name FROM permission.grp_tree pgt WHERE id = 1 | |
UNION ALL | |
SELECT pgt.id, pgt.parent, profile_tree.name || ' -> ' || pgt.name | |
FROM permission.grp_tree pgt | |
INNER JOIN profile_tree ON pgt.parent = profile_tree.id | |
WHERE pgt.parent IS NOT NULL | |
) | |
SELECT profile_tree.name AS profile, COUNT(*) AS lost_count | |
FROM asset.copy acp | |
JOIN config.copy_status ccs ON acp.status = ccs.id | |
-- rely on action.open_circulation view rather than trying to use | |
-- something like "most recent circulation" | |
JOIN action.open_circulation ocirc ON ocirc.target_copy = acp.id | |
JOIN actor.usr au ON ocirc.usr = au.id | |
JOIN profile_tree ON au.profile = profile_tree.id | |
WHERE acp.circ_lib IN (SELECT id FROM actor.org_unit_descendants(1)) -- option to limit by copy circ lib | |
AND acp.status IN (3, 17) -- "Lost", "Lost and Paid" | |
AND ocirc.stop_fines = 'LOST' | |
-- uncomment to exclude deleted copies | |
--AND NOT acp.deleted | |
-- uncomment to exclude where transaction is closed (usually because of zero balance) | |
--AND ocirc.xact_finish IS NULL | |
GROUP BY profile_tree.name | |
ORDER BY profile_tree.name; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- Get an idea of how well "Lost" and "Lost and Paid" copy status match up | |
-- with "LOST" stop_fines in your database | |
-- Databases with migrations in their history may differ greatly | |
SELECT COALESCE(ocirc.stop_fines, '[no open circ]') AS stop_fines, ccs.name AS status, count(*) | |
FROM asset.copy acp | |
JOIN config.copy_status ccs ON acp.status = ccs.id | |
LEFT JOIN action.open_circulation ocirc ON ocirc.target_copy = acp.id | |
WHERE acp.status IN (3, 17) -- "Lost", "Lost and Paid" | |
OR ocirc.stop_fines = 'LOST' | |
GROUP BY ocirc.stop_fines, ccs.name | |
ORDER BY ocirc.stop_fines, ccs.name; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
stop_fines | status | count | |
----------------+--------------------------+------- | |
CLAIMSRETURNED | Lost | 5 | |
LOST | Checked out | 280 | |
LOST | Damaged | 2 | |
LOST | Lost | 27079 | |
[no open circ] | Lost | 1 | |
(5 rows) |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment