Patrons Blocked by Overdues - Total Outstanding Balances
This query is useful for fine free proposals. It gets patron overdue information along with activity information. This specifically looks at overdue fines and nothing else, though the query can be easily modified to do so. In addition, it pulls the total number of overdue fines on patron accounts and provides their last activity date.
SELECT
ac.PatronID AS [Patron ID],
o.Name AS [Patron Branch],
frc.FeeDescription AS [Fee Reason],
CAST(STR(SUM(ISNULL(ac.OutstandingAmount, 0)), 12, 2) AS DECIMAL (20,2)) AS [Balance Total],
COUNT(DISTINCT ac.TxnID) AS [Number of Transactions],
p.LastActivityDate AS [Last Activity Date]
FROM
Polaris.Polaris.PatronAccount ac WITH (NOLOCK)
LEFT JOIN
Polaris.Polaris.FeeReasonCodes frc WITH (NOLOCK) ON (ac.FeeReasonCodeID = frc.FeeReasonCodeID)
INNER JOIN
Polaris.Polaris.Patrons p WITH (NOLOCK) ON (p.PatronID = ac.PatronID)
INNER JOIN
Polaris.Polaris.Organizations o WITH (NOLOCK) ON (o.OrganizationID = p.OrganizationID)
INNER JOIN
Polaris.Polaris.PatronRegistration pr WITH (NOLOCK) ON (p.PatronID = pr.PatronID)
WHERE -- Put your OrgIDs below
ac.OrganizationID IN (89)
AND -- Adjust birthdates if needed - can be used to limit to adults and juveniles
pr.Birthdate BETWEEN '2003-10-28 00:00:00.000' AND '2022-10-28 23:59:59.999'
AND ac.TxnID IN (
SELECT
DISTINCT TxnID
FROM
Polaris.Polaris.PatronAccount WITH (NOLOCK)
WHERE
OutstandingAmount > 0
AND TxnCodeID = 1
)
AND -- FeeReasonCodeID 0 = Overdues
ac.FeeReasonCodeID IN (0)
AND -- Adjust expirations if needed
pr.ExpirationDate BETWEEN '1888-01-01' AND '2021-10-28'
GROUP BY
ac.PatronID,
o.Name,
frc.FeeDescription,
p.LastActivityDate
HAVING
CAST(STR(SUM(ISNULL(ac.OutstandingAmount, 0)), 12, 2) AS DECIMAL (20,2)) >= 5.00 -- Change this last number as desired for your blocking threshold
ORDER BY
--[Balance Total] DESC
p.LastActivityDate ASC