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