Hi,
We are upgrading to 7.8 during Christmas week because our director has decided she wants to waive all fines older than X and below Y by the end of the year. We have it updated on the training server, and I found where the bulk waive function is performed; however, it isn’t as granular as she is requesting. I am no SQL expert (at all), and I was hoping some wonderful person in the forums has already tackled this subject (although I did search).
TIA
Vicky
When you say “below Y” do you mean that the individual fine was below that dollar amount or that if you were to sum all outstanding balances from the cutoff total that the total account balance would have been below that amount?
So, for example:
or
This lightly tested script will work for option #1 and is some LLM mixed with code adapted from this script - NOTE the mode switch from DETAIL TO SUMMARY.
/*
Script: Outstanding Fines Analysis (Summary / Detail Modes)
Platform: Microsoft SQL Server
Modes:
- 'SUMMARY' = grouped totals by fee reason
- 'DETAIL' = matching PatronAccount TxnID list only (deduped)
*/
-- =============================================
-- 1) MODE SWITCH
-- =============================================
DECLARE @Mode VARCHAR(10) = 'DETAIL'; -- set to 'DETAIL' or 'SUMMARY'
-- =============================================
-- 2) PARAMETERS
-- =============================================
-- If NULL/empty => ALL branches; else comma-separated list (e.g. '89,90,91')
DECLARE @OrganizationIDs NVARCHAR(MAX) = NULL;
-- If NULL/empty => ALL fee reasons; else comma-separated list (e.g. '1,2,4')
DECLARE @FeeReasonCodeIDs NVARCHAR(MAX) = NULL;
-- Filter: include fines LESS THAN this amount
DECLARE @MinBalance DECIMAL(19,2) = 5.00;
DECLARE @CutoffDate DATE = '2023-01-01';
-- =============================================
-- 3) NORMALIZE LISTS INTO TABLE VARIABLES
-- =============================================
DECLARE @OrgFilter TABLE (OrganizationID INT PRIMARY KEY);
DECLARE @ReasonFilter TABLE (FeeReasonCodeID INT PRIMARY KEY);
IF NULLIF(LTRIM(RTRIM(@OrganizationIDs)), '') IS NOT NULL
BEGIN
INSERT INTO @OrgFilter (OrganizationID)
SELECT DISTINCT TRY_CAST(LTRIM(RTRIM(value)) AS INT)
FROM STRING_SPLIT(@OrganizationIDs, ',')
WHERE TRY_CAST(LTRIM(RTRIM(value)) AS INT) IS NOT NULL;
END;
IF NULLIF(LTRIM(RTRIM(@FeeReasonCodeIDs)), '') IS NOT NULL
BEGIN
INSERT INTO @ReasonFilter (FeeReasonCodeID)
SELECT DISTINCT TRY_CAST(LTRIM(RTRIM(value)) AS INT)
FROM STRING_SPLIT(@FeeReasonCodeIDs, ',')
WHERE TRY_CAST(LTRIM(RTRIM(value)) AS INT) IS NOT NULL;
END;
-- =============================================
-- 4) MATERIALIZE FILTERED DATA ONCE (TEMP TABLE)
-- =============================================
IF OBJECT_ID('tempdb..#Filtered') IS NOT NULL
DROP TABLE #Filtered;
SELECT
ac.TxnID,
ac.PatronID,
ac.FeeReasonCodeID,
ac.OutstandingAmount
INTO #Filtered
FROM Polaris.Polaris.PatronAccount ac WITH (NOLOCK)
WHERE
ac.TxnCodeID = 1
AND ac.OutstandingAmount <= @MinBalance
AND ac.OutstandingAmount != 0.00
AND ac.TxnDate < DATEADD(DAY, 1, @CutoffDate) -- safe when TxnDate is datetime
-- Branch filter (ALL if list is null/empty)
AND (
NOT EXISTS (SELECT 1 FROM @OrgFilter)
OR EXISTS (
SELECT 1
FROM @OrgFilter f
WHERE f.OrganizationID = ac.OrganizationID
)
)
-- Fee reason filter (ALL if list is null/empty)
AND (
NOT EXISTS (SELECT 1 FROM @ReasonFilter)
OR EXISTS (
SELECT 1
FROM @ReasonFilter f
WHERE f.FeeReasonCodeID = ac.FeeReasonCodeID
)
);
-- =============================================
-- 5) OUTPUT (MODE SWITCH)
-- =============================================
IF UPPER(@Mode) = 'DETAIL'
BEGIN
-- DETAIL MODE: TxnID only (deduped)
SELECT *
FROM #Filtered
ORDER BY TxnID;
END
ELSE
BEGIN
-- SUMMARY MODE: grouped totals by fee reason
SELECT
f.FeeReasonCodeID,
COALESCE(frc.FeeDescription, 'Unknown/Other') AS [Reason],
CAST(SUM(f.OutstandingAmount) AS DECIMAL(20,2)) AS [Balance Total],
COUNT(DISTINCT f.TxnID) AS [Number of Transactions],
COUNT(DISTINCT f.PatronID) AS [Number of Patrons]
FROM #Filtered f
LEFT JOIN Polaris.Polaris.FeeReasonCodes frc WITH (NOLOCK)
ON frc.FeeReasonCodeID = f.FeeReasonCodeID
GROUP BY
f.FeeReasonCodeID,
frc.FeeDescription
ORDER BY
COALESCE(frc.FeeDescription, 'Unknown/Other');
END;
-- Optional cleanup (not required; temp table auto-drops at session end)
-- DROP TABLE #Filtered;
I am not sure what she is thinking yet. But my theory is a certain dollar amount prior to a date. Currently I am gathering as much information from the bulk waive as I can.
Thanks,
Vicky
Oof, you know, one thing I forgot about is that this uses a temp table, I’m not sure if that will work in the fine waiving tool.
I’m sure there is a way to simplify it, but I was trying to give you a method that would allow you to experiment with different options. Once you hear back exactly what they want, we could attempt some further tweaks.
Keep in mind, Last Activity Date will likely be updated as a result of changes, especially if you have Overdrive integration… we had to have Innovative do our bulk waive and reset the last activity dates…
If you’re looking for something that would waive fees of a certain monetary value (i.e. anything under $1 that was assessed prior to a certain date - for instance if you went fine free recently and want to remove any old overdue fines) you might be looking for something like this:
select txnid from patronaccount with (nolock)
where txncodeid=1 --a charge
and outstandingamount < 1 --outstanding amount is under a certain amount
and outstandingamount != 0 --no point waiving charges that were already paid
and txndate < dateadd(year,-5,getdate()) --date the fee was assessed is more than 5 years ago
I don’t think the waive tool will like the comments, so maybe something like this:
select txnid from patronaccount with (nolock)
where txncodeid=1
and outstandingamount < 1
and outstandingamount != 0
and txndate < dateadd(year,-5,getdate())
If you want to check these criteria in the find tool, you’d need to do a search for patron records with this query - you’ll still need to sort through which of their fines would be waived, of course:
select patronid from patronaccount with (nolock)
where txncodeid=1
and outstandingamount < 1
and outstandingamount != 0
and txndate < dateadd(year,-5,getdate())
I just did a little testing. I opened a new bulk waive criteria and pasted Wes’s SQL into the box. (I had modified it to give just a list of Transaction IDs, as required for the bulk waive.) Then I clicked “Test Criteria” and got the error message “The query must be only a select statement.” So it looks like temp tables are not allowable in this context. Here’s a very basic SQL statement that will waive all outstanding charges below a certain amount made prior to a certain date. It assumes that when the director says “fines” they mean charges for overdue items only - not other kinds of charges. If they want to waive all types of charges, delete the two indicated lines.
select pa.TxnID
from
Polaris.Polaris.PatronAccount pa (nolock)
where
-- the transaction is a charge to the patron's account
pa.TxnCodeID = 1
and
-- the charge is for an overdue item (no other types of charges will be waived)
-- if you want to waive all types of charges, delete the next two lines
pa.FeeReasonCodeID = 0
and
-- money is still owed for this charge
pa.OutstandingAmount > 0
and
-- charge was made prior to this date (M-D-YY)
pa.TxnDate < '1-1-21'
and
-- the original amount charged was less than this (in dollars)
pa.TxnAmount < .5
ADDED - Ah - I see Eleanor had already suggested just this solution.
FWIW, you can include comments in the SQL for your bulk waive criteria.
Meanwhile, Vicky’s question made me look closely at the other parts of the bulk waive form (where you can specify criteria without using SQL) and I noticed that a maximum or minimum amount of the charge is not an option. This seems like a pretty obvious criterion, so I’ve added a suggestion to the Idea Exchange: Bulk waive criteria should allow user to specify maximum or minimum amount of charge to be waived – Innovate with us. Vote early and often!
Thank you. I will share this with my manager and director.
Vicky
Thanks, I will share this with our director and my manager.
Vicky
I think that the limit with the built-in filtering is that it doesn’t include a $ amount balance, hence the enhancement request: Bulk waive criteria should allow user to specify maximum or minimum amount of charge to be waived – Innovate with us
