# Deleted Items Billed and Overdue
**URL:** https://forum.innovativeusers.org/t/deleted-items-billed-and-overdue/2513
**Category:** Polaris Repository
**Tags:** sql, polaris-sql, polaris-sys-admin, polaris
**Created:** [June 13, 2025, 6:22pm UTC](https://forum.innovativeusers.org/t/deleted-items-billed-and-overdue/2513 "2025-06-13T18:22:58Z")
**Posts on this page:** 6
**Page:** 1
### Author:  [@CyberpunkLibrarian](https://forum.innovativeusers.org/u/CyberpunkLibrarian)
#### Post date: [June 13, 2025, 6:22pm UTC](https://forum.innovativeusers.org/t/deleted-items-billed-and-overdue/2513/1 "2025-06-13T18:22:58Z")
This query looks for overdue and replacement charges on patron accounts that are tied to items that have been deleted and removed from the system.
```auto
SELECT
pr.PatronFullName AS [Patron Name],
p.Barcode AS [Patron Barcode],
po.DisplayName AS [Patron Branch],
padir.ItemRecordID,
padir.BrowseTitle AS [Title],
padir.BrowseAuthor AS [Author],
padir.MaterialType AS [Material Type],
frc.FeeDescription AS [Fee Description],
CAST(pa.OutstandingAmount AS DECIMAL(20,2)) AS [Outstanding Balance]
FROM
Polaris.Polaris.PatronAccount pa WITH (NOLOCK)
INNER JOIN
Polaris.Polaris.Patrons p WITH (NOLOCK)
ON (p.PatronID = pa.PatronID)
INNER JOIN
Polaris.Polaris.PatronRegistration pr WITH (NOLOCK)
ON (p.PatronID = pr.PatronID)
INNER JOIN -- Patron assigned branch
Polaris.Polaris.Organizations po WITH (NOLOCK)
ON (po.OrganizationID = p.OrganizationID)
INNER JOIN
Polaris.Polaris.FeeReasonCodes frc WITH (NOLOCK)
ON (frc.FeeReasonCodeID = pa.FeeReasonCodeID)
INNER JOIN -- Pull in deleted item records
Polaris.Polaris.PatronAcctDeletedItemRecords padir WITH (NOLOCK)
ON (padir.ItemRecordID = pa.ItemRecordID)
WHERE -- Look for patrons with outstanding balances
pa.TxnID IN (
SELECT DISTINCT TxnID
FROM Polaris.Polaris.PatronAccount WITH (NOLOCK)
WHERE TxnCodeID IN (1)
AND OutstandingAmount > 0)
AND -- 0 = Overdue and -1 = Replacement Cost
pa.FeeReasonCodeID IN (-1,0)
ORDER BY
po.DisplayName,
padir.ItemRecordID
```
---
### Author:  [@TamiMiller2023](https://forum.innovativeusers.org/u/TamiMiller2023)
#### Post date: [June 26, 2025, 5:38pm UTC](https://forum.innovativeusers.org/t/deleted-items-billed-and-overdue/2513/2 "2025-06-26T17:38:26Z")
Hi Daniel,
Can you write this so it shows patrons who have lost items linked to them, but the item has been deleted? I’d like to have the Patron Name, Patron Barcode, Title of Item, and Item Barcode. Thanks for any help you can provide.
---
### Author:  [@CyberpunkLibrarian](https://forum.innovativeusers.org/u/CyberpunkLibrarian)
#### Post date: [June 26, 2025, 6:14pm UTC](https://forum.innovativeusers.org/t/deleted-items-billed-and-overdue/2513/3 "2025-06-26T18:14:19Z")
Ah! So kind of the reverse of this, right? You’re looking for the patron first, and the item second.
Yeah, I gotta do some SQL work over the next day or two anyway so I think I can throw this in! 😃 I’ll post about it and tag you when it’s ready!
---
### Author:  [@TamiMiller2023](https://forum.innovativeusers.org/u/TamiMiller2023)
#### Post date: [June 26, 2025, 7:01pm UTC](https://forum.innovativeusers.org/t/deleted-items-billed-and-overdue/2513/4 "2025-06-26T19:01:26Z")
Thank you!! I have a couple other scripts that I used to have, and now I can’t find them. Can I email you?
---
### Author:  [@CyberpunkLibrarian](https://forum.innovativeusers.org/u/CyberpunkLibrarian)
#### Post date: [June 26, 2025, 7:47pm UTC](https://forum.innovativeusers.org/t/deleted-items-billed-and-overdue/2513/5 "2025-06-26T19:47:50Z")
Sure! daniel.messer at lsslibraries dot com will work!
---
### Author:  [@CyberpunkLibrarian](https://forum.innovativeusers.org/u/CyberpunkLibrarian)
#### Post date: [June 27, 2025, 6:22pm UTC](https://forum.innovativeusers.org/t/deleted-items-billed-and-overdue/2513/6 "2025-06-27T18:22:28Z")
Okay, I’ve never dropped code into a reply before, but I see no reason why that shouldn’t work. That in mind, this query is a sort-of reverse process from the Deleted Items Billed and Overdue query. Instead of looking for deleted items and the patrons they’re attached to, this query looks for _patrons_ and the deleted items attached to _them._
```auto
SELECT
pr.PatronFullName AS [Patron Name],
p.Barcode AS [Patron Barcode],
padir.BrowseTitle AS [Title],
padir.Barcode AS [Item Barcode],
frc.FeeDescription AS [Fee Description]
FROM
Polaris.Polaris.PatronAccount pa WITH (NOLOCK)
INNER JOIN
Polaris.Polaris.Patrons p WITH (NOLOCK)
ON (p.PatronID = pa.PatronID)
INNER JOIN
Polaris.Polaris.PatronRegistration pr WITH (NOLOCK)
ON (pr.PatronID = pa.PatronID)
INNER JOIN
Polaris.Polaris.FeeReasonCodes frc WITH (NOLOCK)
ON (frc.FeeReasonCodeID = pa.FeeReasonCodeID)
INNER JOIN
Polaris.Polaris.PatronAcctDeletedItemRecords padir WITH (NOLOCK)
ON (padir.ItemRecordID = pa.ItemRecordID)
WHERE -- Look for patrons with outstanding balances
pa.TxnID IN (
SELECT DISTINCT TxnID
FROM Polaris.Polaris.PatronAccount WITH (NOLOCK)
WHERE TxnCodeID IN (1)
AND OutstandingAmount > 0)
AND -- 0 = Overdue and -1 = Replacement Cost
pa.FeeReasonCodeID IN (-1,0)
ORDER BY
pr.PatronFullName,
padir.BrowseTitle
```