Total Fines Owed by Patrons by Fee Reason

Clearinghouse Repository Polaris Repository
,
1

Total Fines Owed by Patrons by Fee Reason

This rather lengthy query is a modification of a SimplyReports query. This one provides the total balances, transactions, and number of patrons with fines and fees and breaks them down by Fee Reason. Useful for fine free projects.
```SQL SELECT frc.FeeDescription AS [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], COUNT(DISTINCT ac.PatronID) AS [Number of Patrons]

FROM
Polaris.Polaris.PatronAccount ac WITH (NOLOCK)

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
Polaris.Polaris.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 – Adjust the OrgID as needed
ac.OrganizationID in (89)
AND
ac.TxnID in (
SELECT
DISTINCT TxnID
FROM
Polaris.Polaris.PatronAccount WITH (NOLOCK)
WHERE
OutstandingAmount > 0
AND
TxnCodeID = 1
AND
TxnDate BETWEEN ‘1888-01-01 00:00:00.000’ AND ‘2018-10-27 23:59:59.999’
)
AND
ac.FeeReasonCodeID in (-12,-11,-10,-9,-8,-7,-6,-5,-4,-3,-2,-1,0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31)
GROUP BY
frc.FeeDescription