Patrons Blocked for Overdues Alone
Another useful query for fine free proposals. This query pulls a list of patrons who owe overdue fines and nothing else. In
other words, they don’t owe replacement, processing, lost card, or any other charges. It also includes a parameter so you can
set a blocking threshold, showing how many of these patrons are blocked from using their accounts because of the overdues.
-- I'm limiting this to a given branch, but you can remove this and include your own OrganizationID codes in the query.
DECLARE @OrgID INT;
SET @OrgID = 89
/* BEGIN TEMP TABLE SET UP AND POPULATION */
-- Set up a temp table to hold onto patrons who owe money for everything *except* overdues.
DECLARE @TempPatronAccountFRCExclude TABLE
(PatronID INT)
INSERT INTO @TempPatronAccountFRCExclude
-- Not gonna lie, I stole this buttugly query from SimplyReports. It's heinous, but it works.
SELECT
ac.PatronID
FROM
Polaris.Polaris.PatronAccount ac WITH (NOLOCK)
-- JOIN everything but the kitchen sink.
INNER JOIN
Polaris.Polaris.PolarisUsers pu WITH (NOLOCK) ON (ac.CreatorID = pu.PolarisUserID)
INNER JOIN
Polaris.Polaris.Patrons p WITH (NOLOCK) ON (p.PatronID = ac.PatronID)
INNER JOIN
Polaris.Polaris.PatronRegistration pr WITH (NOLOCK) ON (p.PatronID = pr.PatronID)
INNER JOIN
Polaris.Polaris.Organizations gov WITH (NOLOCK) ON (ac.OrganizationID = gov.OrganizationID)
INNER JOIN
Organizations o WITH (NOLOCK) ON (p.OrganizationID = o.OrganizationID)
INNER JOIN
Polaris.Polaris.PatronCodes pc WITH (NOLOCK) ON (p.PatronCodeID = pc.PatronCodeID)
INNER JOIN
Polaris.Polaris.PatronAccTxnCodes txncd WITH (NOLOCK) ON (ac.TxnCodeID = txncd.TxnCodeID)
LEFT JOIN
Polaris.Polaris.FeeReasonCodes frc WITH (NOLOCK) ON (ac.FeeReasonCodeID = frc.FeeReasonCodeID)
LEFT JOIN
Polaris.Polaris.PatronPaymentMethods ppm WITH (NOLOCK) ON (ac.PaymentMethodID = ppm.PaymentMethodID)
LEFT JOIN
Polaris.Polaris.Workstations pws WITH (NOLOCK) ON (ac.WorkstationID = pws.WorkstationID)
LEFT JOIN
Polaris.Polaris.PatronAddresses pa WITH (NOLOCK) ON (pr.PatronID = pa.PatronID and pa.AddressTypeID = 2 )
LEFT JOIN
Polaris.Polaris.Addresses addr WITH (NOLOCK) ON (pa.AddressID = addr.AddressID)
LEFT JOIN
Polaris.Polaris.PostalCodes pos WITH (NOLOCK) ON (addr.PostalCodeID = pos.PostalCodeID)
LEFT JOIN
Polaris.Polaris.PatronStatClassCodes sc WITH (NOLOCK) ON (pr.StatisticalClassID = sc.StatisticalClassID)
LEFT JOIN
Polaris.Polaris.ItemRecordDetails ird WITH (NOLOCK) ON (ac.ItemRecordID = ird.ItemRecordID)
LEFT JOIN
Polaris.Polaris.CircItemRecords cir WITH (NOLOCK) ON (ac.ItemRecordID = cir.ItemRecordID)
LEFT JOIN
Polaris.Polaris.BibliographicRecords br WITH (NOLOCK) ON (cir.AssociatedBibRecordID = br.BibliographicRecordID)
LEFT JOIN
Polaris.Polaris.organizations oi WITH (NOLOCK) ON (cir.AssignedBranchID = oi.OrganizationID)
LEFT JOIN
Polaris.Polaris.Collections col WITH (NOLOCK) ON (cir.AssignedCollectionID = col.CollectionID)
LEFT JOIN
Polaris.Polaris.MaterialTypes mt WITH (NOLOCK) ON (cir.MaterialTypeID = mt.MaterialTypeID)
LEFT JOIN
Polaris.Polaris.ItemStatuses ist WITH (NOLOCK) ON (cir.ItemStatusID = ist.ItemStatusID)
WHERE
ac.OrganizationID IN (@OrgID)
AND
ac.TxnID IN (
SELECT
DISTINCT TxnID
FROM
Polaris.Polaris.PatronAccount with (nolock)
WHERE
OutstandingAmount > 0
AND
TxnCodeID = 1)
AND
ac.FeeReasonCodeID NOT IN (0) -- In other words, they owe money for everything except overdue fines.
GROUP BY
ac.PatronID,
frc.FeeDescription
ORDER BY
ac.PatronID
/* END TEMP TABLE SET UP AND POPULATION */
-- Using the table created above, pull a list of patrons who owe money for overdues, and *only* overdues
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 (@OrgID)
--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 '2028-10-28'
AND
ac.PatronID NOT IN (
SELECT PatronID FROM @TempPatronAccountFRCExclude -- If they owe overdues, and do *not* appear in the temp table, then they *only* owe for overdues.
)
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
[Patron ID]