This query pulls data on deleted items connected to outstanding balances on patron accounts. Item deletion dates are pulled from PolarisTransactions and paired with data from the Polaris database to provide PatronID, total and specific outstanding balances, item and patron barcodes, fee reasons, and titles.
-- Let's use a CTE so this'll work at hosted libraries
-- This pulls the specific item deleted transaction for items in the PatronAcctDeletedItemRecords table
WITH DeletedItemData AS (
SELECT
item.numValue AS [ItemRecordID],
th.TranClientDate AS [DeleteDate]
FROM
PolarisTransactions.Polaris.TransactionHeaders th WITH (NOLOCK)
INNER JOIN -- Get the ItemRecordID
PolarisTransactions.Polaris.TransactionDetails item WITH (NOLOCK)
ON (th.TransactionID = item.TransactionID AND item.TransactionSubtypeID = 38)
LEFT JOIN -- Bring in the deleted items still connected to patron accounts
Polaris.Polaris.PatronAcctDeletedItemRecords padir WITH (NOLOCK)
ON (padir.ItemRecordID = item.numValue)
WHERE -- Item record deleted
TransactionTypeID = 3007
AND -- Only keep the matching item records
padir.ItemRecordID IS NOT NULL
)
SELECT
p.PatronID AS [PatronID],
FORMAT(SUM(pa.OutstandingAmount) OVER (PARTITION BY p.PatronID), '0.00') AS [Total Outstanding Balance],
p.Barcode AS [Patron Barcode],
FORMAT(pa.OutstandingAmount, '0.00') AS [Specific Outstanding Balance],
pa.TxnDate AS [Fee Date],
frc.FeeDescription AS [Fee Reason],
did.ItemRecordID AS [ItemRecordID],
padir.BrowseTitle AS [Title],
padir.Barcode AS [Item Barcode],
did.DeleteDate AS [Date Deleted]
FROM
Polaris.Polaris.PatronAcctDeletedItemRecords padir WITH (NOLOCK)
INNER JOIN -- Bring in the results from the CTE
DeletedItemData did
ON (did.ItemRecordID = padir.ItemRecordID)
INNER JOIN -- Get patron and account data based on ItemRecordID, charges, and oustanding amounts
Polaris.Polaris.PatronAccount pa WITH (NOLOCK)
ON (padir.ItemRecordID = pa.ItemRecordID AND pa.TxnCodeID = 1 AND pa.OutstandingAmount > 0)
INNER JOIN -- Bring in Fee Reasons
Polaris.Polaris.FeeReasonCodes frc WITH (NOLOCK)
ON (frc.FeeReasonCodeID = pa.FeeReasonCodeID)
INNER JOIN -- Get patron data
Polaris.Polaris.Patrons p WITH (NOLOCK)
ON (p.PatronID = pa.PatronID)
ORDER BY
p.PatronID, did.DeleteDate ASC;