1 92 789 2018-05-18 2018-05-17
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