Patrons Blocked by Overdues - Total Outstanding Balances
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