Skip to content

Instantly share code, notes, and snippets.

@jeff jeff/example_output.txt Secret
Last active Aug 29, 2015

Embed
What would you like to do?
Query to count "lost" copies by the profile group of the user that "lost" them.
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)
-- 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;
-- 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;
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
You can’t perform that action at this time.