SQL help for list of patron acct charges

Polaris
1

Hi all!
I have been tasked by my director to come up with a list of patrons, including their charges, and not in collections but owe fees for lost or damaged items where the total is less than $50 dollars. Simply Reports is no help. I need a patron account report but there is no option to exclude all patrons that are in collections.

These are the fields she wants: patron name-barcode-patron code; item price-title-barcode, card has been expired for 5+ years, and the total charges on the account is $49.99 or less, which I can get, but the report must exclude any patron that is in collections.

The list should not be very large since we waive all overdue fees a few years ago. The goal is to possibly waive these charges. But before we consider it, we want to know the impact and cost. A list is one tool to support, or not, this project.

Any suggestions, TIA?

Trish Pelletier
Apache Junction Public Library

2

You may need to get with @wesochuck for a copy of the RDL file but you could look over the Patron Purge Information - CLC Report Definitions (clcohio.org) details and modify the SQL to meet your needs as far as output.

3

Here’s a copy of our Patron Purge Information report:
Patron Purge Information.rdl (61.6 KB)

I also threw together some SQL more targeted at what you were asking for. There’s probably a more elegant way to put it together but this should be good enough for what you’re looking to do.

select *
from (
	select *
			,sum(d.OutstandingAmount) over(partition by d.PatronBarcode) [PatronTotal]
	from (
		select	 pr.NameFirst
				,pr.NameLast
				,p.Barcode		[PatronBarcode]
				,pc.Description [PatronCode]
				,br.SortTitle	[Title]
				,cir.Barcode	[ItemBarcode]
				,ird.Price
				,sum(pa.OutstandingAmount) [OutstandingAmount]
		from polaris.polaris.PatronAccount pa
		join polaris.polaris.patrons p
			on p.PatronID = pa.PatronID
		join polaris.polaris.PatronRegistration pr
			on pr.PatronID = p.PatronID
		join polaris.polaris.PatronCodes pc
			on pc.PatronCodeID = p.PatronCodeID
		join polaris.polaris.CircItemRecords cir
			on cir.ItemRecordID = pa.ItemRecordID
		join polaris.polaris.ItemRecordDetails ird
			on ird.ItemRecordID = cir.ItemRecordID
		join polaris.polaris.BibliographicRecords br
			on br.BibliographicRecordID = cir.AssociatedBibRecordID
		where pa.TxnCodeID = 1 -- charge
			and pa.OutstandingAmount > 0 -- still owes on charge
			and pa.FeeReasonCodeID in (-1,-6,-12) -- Replacement Cost, Processing Charge, Missing Parts
			and p.SystemBlocks & 1024 != 1024 -- not in collections
			and pr.ExpirationDate < dateadd(year, -5, getdate()) -- expired for 5+ years
		group by p.PatronID, pr.NameFirst, pr.NameLast, p.Barcode, pc.Description, ird.Price, br.SortTitle, cir.Barcode
	) d
) d2
where d2.PatronTotal < 50 -- total charges less than $50
order by d2.NameFirst, d2.NameLast, d2.Title, d2.OutstandingAmount
1 Like