Patron Account Summary with Counts

Clearinghouse Repository Polaris Repository
,
1

Patron Account Summary with Counts

This query pulls transaction summary information for a branch or library along with a given set of fee reasons, number of transactions, number of patrons, and the total amount for those transactions. Particularly useful when working on fine free projects.
```SQL SELECT o.Name AS [Library/Branch], frc.FeeDescription AS [Fee Reason], COUNT(DISTINCT th.TransactionID) AS NumberOfTransactions, COUNT(DISTINCT patron.numValue) AS [Patrons], STR(ROUND(CAST(SUM(mon.numValue/1000.00) AS MONEY),2),20,2) AS [Total Amount]

FROM
PolarisTransactions.Polaris.TransactionHeaders th WITH (NOLOCK)
INNER JOIN
PolarisTransactions.Polaris.TransactionDetails td WITH (NOLOCK) ON (th.TransactionID = td.TransactionID)
INNER JOIN – This is the join that gets the monetary information.
PolarisTransactions.Polaris.TransactionDetails mon WITH (NOLOCK) ON (th.TransactionID = mon.TransactionID and mon.TransactionSubTypeID = 141)
INNER JOIN – This is the join that gets the patron information.
PolarisTransactions.Polaris.TransactionDetails patron WITH (NOLOCK) on (th.TransactionID = patron.TransactionID and patron.TransactionSubTypeID = 6)
INNER JOIN
Polaris.Polaris.Organizations o WITH (NOLOCK) ON (th.OrganizationID = o.OrganizationID)
LEFT JOIN
Polaris.Polaris.FeeReasonCodes frc WITH (NOLOCK) ON (td.numValue = frc.FeeReasonCodeID)

WHERE – 6014 = Amount charged | 6016 = Amount paid | 6018 = Amount waived
th.TransactionTypeID = 6014 AND td.TransactionSubTypeID = 8
AND – Adjust dates as needed
th.TranClientDate BETWEEN ‘2019-07-01 00:00:00.000’ and ‘2020-06-30 23:59:59.999’
AND – Add your OrgIDs
th.OrganizationID IN (89)
AND – You can plug in different FeeReasonCodeIDs below. 0 = Overdues
td.numvalue IN (0)

GROUP BY
o.Name,
frc.FeeDescription

ORDER BY
o.Name,
frc.FeeDescription