This script shows patrons who were sent Fine Notices via email during the dates specified.
This is NOT when the Fine was generated for the account, but when the NOTICE was sent by Polaris.
DECLARE @DeliveryOption VARCHAR(50) = 'Email Address';
DECLARE @NotificationDescriptionLike VARCHAR(50) = '%Fine%';
DECLARE @StartDate DATE = '2025-05-15';
DECLARE @EndDate DATE = '2025-05-29';
SELECT
p.PatronID,
p.Barcode,
pr.NameFirst,
pr.NameMiddle,
pr.NameLast,
pr.EmailAddress,
nh.Amount AS FineAmountInNotification,
nh.NoticeDate,
sdo.DeliveryOption AS DeliveryMethodDescription,
nt.Description AS NotificationTypeDescription
FROM
Results.Polaris.NotificationHistory AS nh
JOIN
Polaris.Polaris.Patrons AS p
ON nh.PatronID = p.PatronID
JOIN
Polaris.Polaris.PatronRegistration AS pr
ON p.PatronID = pr.PatronID
JOIN
Polaris.Polaris.SA_DeliveryOptions AS sdo
ON nh.DeliveryOptionID = sdo.DeliveryOptionID
JOIN
Polaris.Polaris.NotificationTypes AS nt
ON nh.NotificationTypeID = nt.NotificationTypeID
WHERE
sdo.DeliveryOption = @DeliveryOption -- Or your system's specific description for email, e.g., 'E-mail'
AND (
nt.Description LIKE @NotificationDescriptionLike -- Catches 'Fine Notice', 'Overdue Fine', etc.
) -- Verify these descriptions against your Polaris.Polaris.NotificationTypes table
AND nh.NoticeDate >= @StartDate
AND nh.NoticeDate < DATEADD(day, 1, @EndDate)
A requested variation that only shows those where the fine notice went out, and the account had fines that were before the start date of looking for when the fine notice was generated. This seems to be a noted problem with Sierra to Polaris migrations.
DECLARE @DeliveryOption VARCHAR(50) = 'Email Address';
DECLARE @NotificationDescriptionLike VARCHAR(50) = '%Fine%';
DECLARE @StartDate DATE = '2025-05-15';
DECLARE @EndDate DATE = '2025-05-29';
SELECT
p.PatronID,
p.Barcode,
pr.NameFirst,
pr.NameMiddle,
pr.NameLast,
pr.EmailAddress,
nh.Amount AS FineAmountInNotification,
nh.NoticeDate,
sdo.DeliveryOption AS DeliveryMethodDescription,
nt.Description AS NotificationTypeDescription,
pa.TxnDate AS ChargeTransactionDate,
pa.TxnAmount AS ChargeTransactionAmount,
patc.Description AS ChargeTypeDescription,
pa.OutstandingAmount AS ChargeOutstandingAmount -- Added for visibility of the new condition
FROM
Results.Polaris.NotificationHistory AS nh
JOIN
Polaris.Polaris.Patrons AS p
ON nh.PatronID = p.PatronID
JOIN
Polaris.Polaris.PatronRegistration AS pr
ON p.PatronID = pr.PatronID
JOIN
Polaris.Polaris.SA_DeliveryOptions AS sdo
ON nh.DeliveryOptionID = sdo.DeliveryOptionID
JOIN
Polaris.Polaris.NotificationTypes AS nt
ON nh.NotificationTypeID = nt.NotificationTypeID
JOIN
Polaris.Polaris.PatronAccount AS pa
ON p.PatronID = pa.PatronID
JOIN
Polaris.Polaris.PatronAccTxnCodes AS patc
ON pa.TxnCodeID = patc.TxnCodeID
WHERE
sdo.DeliveryOption = @DeliveryOption
AND nt.Description LIKE @NotificationDescriptionLike
AND nh.NoticeDate >= @StartDate
AND nh.NoticeDate < DATEADD(day, 1, @EndDate)
AND pa.TxnDate < @StartDate -- Charge was added before @StartDate
AND patc.Description LIKE '%Charge%' -- Transaction is a charge
-- You might need to adjust '%Charge%' to be more specific
-- based on your PatronAccTxnCodes table data
-- (e.g., 'Overdue Charge', 'Lost Item Fee').
AND pa.OutstandingAmount > 0; -- The charge still has an outstanding balance