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
1 Reply
Know the answer? Post it — somebody with the same question will find it here.
Sign in to answer this question
It is the same account you read, post and publish with — and you will come straight back to this page.
Nikunj SatasiyaPosted Feb 25, 2021, 6:12 AM