Get Outstanding Fines Including Deleted Item Titles
This query pulls a list of all outstanding fines for all patrons of a given library or libraries. That’s not all that big of a deal, except this query also pulls browse title information from the deleted items table if a patron has a charge for a deleted item.
SELECT
ac.TxnID AS [Transaction ID],
ac.TxnDate AS [Transaction Date],
ac.ItemRecordID AS [Item Record ID],
p.Barcode AS [Patron Barcode],
pr.PatronFullName AS [Patron Name],
o.name AS [Patron Branch],
cir.Barcode AS [Item Barcode],
COALESCE(br.BrowseTitle,padir.BrowseTitle) AS [Browse Title],
ird.CallNumber AS [Call Number],
ird.Price AS [Item Price],
ist.Description AS [Item Status],
frc.FeeDescription AS [Fee Reason],
CAST(ac.OutstandingAmount AS DECIMAL(20,2)) AS [Balance]
FROM
Polaris.Polaris.PatronAccount ac WITH (NOLOCK)
INNER JOIN
Polaris.Polaris.Patrons p WITH (NOLOCK) ON (p.PatronID = ac.PatronID)
INNER JOIN
Polaris.Polaris.PatronRegistration pr WITH (NOLOCK) ON (p.PatronID = pr.PatronID)
INNER JOIN
Polaris.Polaris.Organizations gov WITH (NOLOCK) ON (ac.OrganizationID = gov.OrganizationID)
INNER JOIN
Polaris.Polaris.organizations o WITH (NOLOCK) ON (p.OrganizationID = o.OrganizationID)
INNER JOIN
Polaris.Polaris.FeeReasonCodes frc WITH (NOLOCK) ON (frc.FeeReasonCodeID = ac.FeeReasonCodeID)
LEFT JOIN
Polaris.Polaris.PatronAcctDeletedItemRecords padir WITH (NOLOCK) ON (padir.ItemRecordID = ac.ItemRecordID)
LEFT JOIN
Polaris.Polaris.ItemRecordDetails ird WITH (NOLOCK) ON (ac.ItemRecordID = ird.ItemRecordID)
LEFT JOIN
Polaris.Polaris.CircItemRecords cir WITH (NOLOCK) ON (ac.ItemRecordID = cir.ItemRecordID)
LEFT JOIN
Polaris.Polaris.BibliographicRecords br WITH (NOLOCK) ON (cir.AssociatedBibRecordID = br.BibliographicRecordID)
LEFT JOIN
Polaris.Polaris.ItemStatuses ist WITH (NOLOCK) ON (cir.ItemStatusID = ist.ItemStatusID)
WHERE
ac.OrganizationID IN () -- Put your OrganizationIDs here
AND
ac.TxnID IN (
SELECT
DISTINCT TxnID
FROM
Polaris.Polaris.PatronAccount WITH (NOLOCK)
WHERE
TxnCodeID IN (1)
AND
OutstandingAmount > 0)
ORDER BY
[Patron Branch] ASC,
[Patron Name] ASC