Overdue Counts and Total Charges by Collection
This query pulls the total overdue charges and the number of overdue charges by Collecton for a given library. This allows you to see where your most charged collections are, and the number of charges in that collection. Useful stats to have when working on fine free transitions.
SELECT
c.Name AS "Collection",
COUNT(c.Name) AS "Number of Charges",
CAST(SUM(pa.TxnAmount) AS DECIMAL (20,2)) AS "Total Overdue Charges"
FROM
Polaris.Polaris.PatronAccount pa WITH (NOLOCK)
JOIN
Polaris.Polaris.ItemRecords ir WITH (NOLOCK) ON pa.ItemRecordID = ir.ItemRecordID
JOIN
Polaris.Polaris.Collections c WITH (NOLOCK) ON c.CollectionID = ir.AssignedCollectionID
WHERE
pa.FeeReasonCodeID = 0 -- Overdues
AND
pa.TxnCodeID = 1 -- Patron account charge
AND
pa.ItemAssignedBranchID IN (106,107,108) -- Add OrganizationID codes as needed
AND
pa.TxnDate BETWEEN '2017-01-01 00:00:00.000' AND '2017-12-31 23:59:59.999' -- Adjust dates as desired
GROUP BY
c.Name
ORDER BY
c.Name