# Total Fines Owed by Patrons by Fee Reason

**URL:** https://forum.innovativeusers.org/t/total-fines-owed-by-patrons-by-fee-reason/1225
**Category:** Polaris Repository
**Tags:** cp-migration, polaris-pat-account
**Created:** [June 2, 2023, 9:02pm UTC](https://forum.innovativeusers.org/t/total-fines-owed-by-patrons-by-fee-reason/1225 "2023-06-02T21:02:57Z")
**Posts on this page:** 1
**Page:** 1

<div class="post-metadata">

### Author: ![system](https://forum.innovativeusers.org/uploads/db7787/original/2X/8/8519ee8eef6492f310ea87d4ce482d931f01eef2.png) [@system](https://forum.innovativeusers.org/u/system)
#### Post date: [June 2, 2023, 9:02pm UTC](https://forum.innovativeusers.org/t/total-fines-owed-by-patrons-by-fee-reason/1225/1 "2023-06-02T21:02:57Z")

</div>

# 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.

```auto
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

```