Patron Account Summary with Counts
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