Newest or Oldest Fines and Fees
Pulls the oldest or newest fine or fee in the system. I’ve been asked for this more than once during fine-free transition work.
SELECT TOP 1
ac.TxnDate AS "Transaction Date",
frc.FeeDescription AS Reason,
CAST(ac.OutstandingAmount AS DECIMAL(20, 2)) AS Balance
FROM
Polaris.Polaris.PatronAccount ac WITH (NOLOCK)
INNER JOIN
Polaris.Polaris.Patrons p WITH (NOLOCK) ON (p.PatronID = ac.PatronID)
INNER JOIN
Polaris.Polaris.PatronRegistration pr WITH (NOLOCK) ON (p.PatronID = pr.PatronID)
INNER JOIN
Polaris.Polaris.Organizations gov WITH (NOLOCK) ON (ac.OrganizationID = gov.OrganizationID)
LEFT JOIN
Polaris.Polaris.FeeReasonCodes frc WITH (NOLOCK) ON (ac.FeeReasonCodeID = frc.FeeReasonCodeID)
WHERE
ac.OrganizationID in (1, 2, 3, 4) -- Limit by organization if you like.
AND
ac.TxnID IN (
SELECT
DISTINCT TxnID
FROM
Polaris.Polaris.PatronAccount WITH (NOLOCK)
WHERE
TxnCodeID IN (1)
AND OutstandingAmount > 0
)
ORDER BY
TxnDate ASC -- USE THIS FOR OLDEST FINE/FEE
--TxnDate DESC -- USE THIS FOR NEWEST FINE/FEE