This query was requested in the Discord where someone needed to know more information about who waived certain kinds of fines. You’ll need to adjust the dates within the query and also provide the proper FeeReasonCodeIDs for your own system. Right now, I’ve simply dropped a couple of FeeReasonCodeIDs in there just to have something to look at when it runs.
SELECT
patron.numValue AS [Patron ID],
p.Barcode AS [Patron Barcode],
pr.PatronFullName AS [Patron Name],
item.numValue AS [Item Record ID],
cir.Barcode AS [Item Barcode],
br.BrowseTitle AS [Title],
br.BrowseAuthor AS [Author],
o.Abbreviation AS [Branch/Library],
pu.Name AS [Polaris User],
w.DisplayName AS [Workstation],
frc.FeeDescription AS [Fee Description],
CAST((amount.numValue/1000.00) AS MONEY) AS [Amount Waived]
FROM
PolarisTransactions.Polaris.TransactionHeaders th WITH (NOLOCK)
INNER JOIN -- Get PatronID
PolarisTransactions.Polaris.TransactionDetails patron WITH (NOLOCK)
ON (th.TransactionID = patron.TransactionID and patron.TransactionSubTypeID = 6)
INNER JOIN -- Get ItemRecordID
PolarisTransactions.Polaris.TransactionDetails item WITH (NOLOCK)
ON (th.TransactionID = item.TransactionID AND item.TransactionSubTypeID = 38)
LEFT JOIN -- Get Item information
Polaris.Polaris.CircItemRecords cir WITH (NOLOCK)
ON (cir.ItemRecordID = item.numValue)
LEFT JOIN -- Get Bibliographic information
Polaris.Polaris.BibliographicRecords br WITH (NOLOCK)
ON (br.BibliographicRecordID = cir.AssociatedBibRecordID)
INNER JOIN -- Get PatronAccount information
PolarisTransactions.Polaris.TransactionDetails account WITH (NOLOCK)
ON (th.TransactionID = account.TransactionID AND account.TransactionSubTypeID = 138)
INNER JOIN -- Make use of the PatronAccount information
Polaris.Polaris.PatronAccount pa WITH (NOLOCK)
ON (pa.TxnID = account.numValue)
INNER JOIN -- Get the Fee Reason
Polaris.Polaris.FeeReasonCodes frc WITH (NOLOCK)
ON (frc.FeeReasonCodeID = pa.FeeReasonCodeID)
INNER JOIN -- Get monetary amount
PolarisTransactions.Polaris.TransactionDetails amount WITH (NOLOCK)
ON (th.TransactionID = amount.TransactionID AND amount.TransactionSubTypeID = 141)
LEFT JOIN -- Get patron info
Polaris.Polaris.Patrons p WITH (NOLOCK)
ON (p.PatronID = patron.numValue)
LEFT JOIN -- Get patron registration info
Polaris.Polaris.PatronRegistration pr WITH (NOLOCK)
ON (pr.patronID = patron.numValue)
INNER JOIN -- Get organization info
Polaris.Polaris.Organizations o WITH (NOLOCK)
ON (o.OrganizationID = th.OrganizationID)
INNER JOIN -- Get Polaris User info
Polaris.Polaris.PolarisUsers pu WITH (NOLOCK)
ON (pu.PolarisUserID = th.PolarisUserID)
INNER JOIN -- Get Workstation info
Polaris.Polaris.Workstations w WITH (NOLOCK)
ON (w.WorkstationID = th.WorkstationID)
WHERE -- Adjust dates as necessary
th.TranClientDate BETWEEN '2024-09-20 00:00:00.000' AND '2024-09-30 23:59:59.999'
AND -- Set your FeeReasonCodeIDs
pa.FeeReasonCodeID IN (-1,0,10)
AND -- Waives
th.TransactionTypeID = 6018