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:
- Are you wanting to waive all outstanding individual fines less than $5, that were added before Jan. 1st 2021?
or
- Do you want to waive ALL outstanding fine amounts, regardless of how large each individual fine might be, if the total account had less than $100 in outstanding before Jan. 1st 2021?
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;