Get Last Record From Each Month

Question

Unfortunately SQL doesn't come to me very easily. I have two tables, a Loan table and a LoanPayments table.

LoanPayments Table:

ID (Primary Key), LoanID (matches an ID on loan table), PaymentDate, Amount, etc.

I need a sql statement that can give me the last payment entered on each month (if there is one). My current statement isn't giving me the results. There is also the problem that sometimes there will be a tie for the greatest date in that month, so I need to be able to deal with that too (my idea was to select the largest ID in the case of a tie).

This is what I have so far (I know it's wrong but I don't know why.):

SELECT lp.ID, lp.LoanID, lp.PaymentDate 
FROM LoanPayments lp 
WHERE lp.PaymentDate in (
                          SELECT DISTINCT MAX(PaymentDate) as PaymentDate 
                          FROM LoanPayments 
                          WHERE IsDeleted = 0
                          AND ReturnDate is null 
                          GROUP BY YEAR(PaymentDate), Month(PaymentDate)
                        ) 
AND CAST(PaymentDate as date) >= CAST(DATEADD(mm, -24, GETDATE()) as date)

The last part is just filtering it so I only get the last 24 months of payments. Thanks for your help and for taking the time to help me with this issue.

Answer 1

You could use the ROW_NUMBER() function here:

SELECT *
FROM (SELECT lp.ID, lp.LoanID, lp.PaymentDate
          , ROW_NUMBER() OVER (PARTITION BY YEAR(PaymentDate), Month(PaymentDate) ORDER BY PaymentDate DESC) 'RowRank'
      FROM LoanPayments lp 
     )sub
WHERE RowRank = 1

That's just the most recent PaymentDate for each month, if you wanted it by LoanID you'd add LoanID to the PARTITION BY list. If you were interested in preserving ties you could use RANK() instead of ROW_NUMBER()

Answer 2

STEP 1: Use a windowing function to add a column that holds that max PaymentDate by month

SELECT
  ID,
  LoanID,
  PaymentDate,
  MAX(PaymentDate) OVER(PARTITION BY YEAR(PaymentDate), MONTH(PaymentDate)) AS MaxPaymentDate,
  ROW_NUMBER() OVER(PARTITION BY PaymentDate ORDER BY ID) AS TieBreaker
FROM LoanPayments 
WHERE IsDeleted = 0
AND ReturnDate is null

STEP 2: Filter those results to just the rows you want

SELECT ID,LoanID,PaymentDate
FROM (
  SELECT
    ID,
    LoanID,
    PaymentDate,
    MAX(PaymentDate) OVER(PARTITION BY YEAR(PaymentDate), MONTH(PaymentDate)) AS MaxPaymentDate,
    ROW_NUMBER() OVER(PARTITION BY PaymentDate ORDER BY ID) AS TieBreaker
  FROM LoanPayments 
  WHERE IsDeleted = 0
  AND ReturnDate is null
) t1
WHERE PaymentDate = MaxPaymentDate AND TieBreaker = 1

This method is more efficient than doing a self-join.