Newest or Oldest Fines and Fees

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