Count of Expired Patrons With a Given Amount of Fines
A common question for fine-free transitions is “How many expired patrons are blocked for fines?” This comes up because many expired patrons are expired because their cards are blocked for fines and they never came back to the library. Perhaps they will after the library eliminated overdues.
SELECT
o.Name AS "Library",
count(p.PatronID) AS "Patron Count",
CAST(SUM(p.ChargesAmount) AS DECIMAL(20, 2)) AS "Total Charges"
FROM
Polaris.Polaris.Patrons p WITH (NOLOCK)
JOIN
Polaris.Polaris.PatronRegistration pr WITH (NOLOCK) ON p.PatronID = pr.PatronID
JOIN
Polaris.Polaris.Organizations o WITH (NOLOCK) ON o.OrganizationID = p.OrganizationID
WHERE
pr.ExpirationDate BETWEEN '1890-01-01' AND '2017-08-31'
AND
p.OrganizationID IN (14, 15, 110, 113) -- Limit by organization codes if desired
AND
p.ChargesAmount >= 24.99
GROUP BY
o.Name