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.
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
2 Likes