In SQL Query - How to get Most recent records in custom query

Question

Could you please let me know, I have written a query to handle below conditions, can you please let me know If its anything wrong with this, I am not getting latest records based on Entry Date.

If a Entry date after the return date, please include the Entry date. If there has not been a Entry Date, please leave blank. If there are multiple Entry Date select last entry date.

 

Current Output is

MultiplePayments AccountId PolicyNumber PaymentDate    ReversalDate

1                               92                789                2018-05-18     2018-05-17

 

Based on the tables records latest entry date is - this is correct answer

MultiplePayments AccountId PolicyNumber PaymentDate ReversalDate

1                                   92          789                   2020-06-21 2020-06-20

 

 My Query :-

drop table if exists #tempPolicyDetails;

create table #tempPolicyDetails

(

PolicyNumber nvarchar(10)

)

--Main temp table - It will hold 10k Polices at a time

INSERT INTO #tempPolicyDetails (PolicyNumber) VALUES

('123'),

('456'),

('789')

--DROP Table if exists @tempPolicyReturnDetails;

DECLARE @tempPolicyReturnDetails TABLE (AccountId BIGINT,PolicyNumber NVARCHAR(10), ReversalDate DateTime,EntryDate DateTime)

WHILE EXISTS(SELECT Top 1 1 FROM #tempPolicyDetails) -- It loops for 5K/10K Polices

BEGIN

DECLARE @PolicyNumber NVARCHAR(10)

DECLARE @AccountId BIGINT =0

SELECT TOP 1 @PolicyNumber = PolicyNumber FROM #tempPolicyDetails

SELECT @AccountId =AccountId FROM PolicyInfo where AccountReference = @PolicyNumber

-- This gives all Policy Return (Check or Card) Details

INSERT INTO @tempPolicyReturnDetails (AccountId,PolicyNumber,EntryDate,ReversalDate)

SELECT a.InitialAllocationAccountId,@PolicyNumber,a.EntryDate,b.ReversalDate

from PaymentInfo a JOIN PaymentReversalInfo b ON a.PaymentId = b.PaymentId

where AccountId IN (@AccountId)

ORDER by a.EntryDate DESC

DELETE FROM #tempPolicyDetails WHERE PolicyNumber = @PolicyNumber

END

-- This Gives Result of Payment date is more than Return Date and If multiple payments then it gives latest Payment Date

SELECT

Result.MultiplePayments,Result.AccountId,Result.PolicyNumber,

CASE -- If there has not been a payment, please leave blank.

WHEN Result.EntryDate = ''

THEN ''

ELSE Result.EntryDate

END AS PaymentDate,

Result.ReversalDate

FROM

(

SELECT

ROW_NUMBER() OVER(PARTITION BY AccountId ORDER BY a.EntryDate DESC) AS MultiplePayments,

a.AccountId,a.PolicyNumber,a.EntryDate,a.ReversalDate

FROM @tempPolicyReturnDetails a

WHERE a.EntryDate > a.ReversalDate --Payment date is more than Return Date

) Result

WHERE

MultiplePayments = 1

ORDER BY Result.EntryDate DESC