Table #temp has columns AccountID, LoanSeries, InstNo, EMIDATE.
Each AccountID might have 1 or 2 LoanSeries, and each loanseries has min 30 InstNo, each InstNo has Increasing EMIDATE.
I need to find those records where installmentNo is increasing but EMIDATE is either same or decreasing.
Example:
ACCOUNTid = AccNo1, LoanSeries =1, InstNo 25, EmiDate = '2023-04-01'. But InstNo 26 has emidate ='2023-03-25'. This is wrong as nextEmiDate should be more than PreviousEmiDate. It should have been '2023-04-29'.
Difference between Dates of InstNo 25 and 26 is (-7)
Similarly, AccountID = AccNo2, LoanSeries =2, InstNo=13, EmiDate is '2023-05-21' But InstNo 14 has EmiDate ='2023-05-07'.
Difference between Dates of InstNo 13 and 14 is (-14)
I need to find all such records with a column as Difference between both dates
Here's the solution that I tried.
drop table if exists #temp
CREATE TABLE #TEMP( AccountID nvarchar(50)
,LoanSeries int
,InstNo int
,EMIDate date
)
Insert into #temp
Select 'AccNo1', 1, 01, '2021-05-29' union all
Select 'AccNo1', 1, 02, '2021-06-26' union all
Select 'AccNo1', 1, 03, '2021-07-24' union all
Select 'AccNo1', 1, 04, '2021-08-21' union all
Select 'AccNo1', 1, 05, '2021-09-18' union all
Select 'AccNo1', 1, 06, '2021-10-16' union all
Select 'AccNo1', 1, 07, '2021-11-13' union all
Select 'AccNo1', 1, 08, '2021-12-11' union all
Select 'AccNo1', 1, 09, '2022-01-08' union all
Select 'AccNo1', 1, 10, '2022-02-05' union all
Select 'AccNo1', 1, 11, '2022-03-05' union all
Select 'AccNo1', 1, 12, '2022-04-02' union all
Select 'AccNo1', 1, 13, '2022-04-30' union all
Select 'AccNo1', 1, 14, '2022-05-28' union all
Select 'AccNo1', 1, 15, '2022-06-25' union all
Select 'AccNo1', 1, 16, '2022-07-23' union all
Select 'AccNo1', 1, 17, '2022-08-20' union all
Select 'AccNo1', 1, 18, '2022-09-17' union all
Select 'AccNo1', 1, 19, '2022-10-15' union all
Select 'AccNo1', 1, 20, '2022-11-12' union all
Select 'AccNo1', 1, 21, '2022-12-10' union all
Select 'AccNo1', 1, 22, '2023-01-07' union all
Select 'AccNo1', 1, 23, '2023-02-04' union all
Select 'AccNo1', 1, 24, '2023-03-04' union all
Select 'AccNo1', 1, 25, '2023-04-01' union all
Select 'AccNo1', 1, 26, '2023-03-25' union all
Select 'AccNo1', 1, 27, '2023-05-27' union all
Select 'AccNo1', 1, 28, '2023-06-24'
Tried this solution:
;WITH CTE AS (
SELECT *,
LAG(EMIDate) OVER (PARTITION BY AccountID, LoanSeries ORDER BY InstNo) AS PrevEMIDate
FROM #temp
)
SELECT t1.AccountID
, t1.LoanSeries
, t1.InstNo
, t1.EMIDate
, t2.InstNo AS PrevInstNo
, t2.EMIDate AS PrevEMIDate
, DATEDIFF(day, t2.EMIDate, t1.EMIDate) AS PrevDiff
, t3.InstNo AS NextInstNo
, t3.EMIDate AS NextEMIDate
, DATEDIFF(day, t1.EMIDate, t3.EMIDate) AS NextDiff
FROM CTE t1
LEFT JOIN CTE t2
ON t1.InstNo = t2.InstNo + 1
LEFT JOIN CTE t3
ON t1.InstNo = t3.InstNo - 1
WHERE t2.EMIDate > t1.EMIDate OR t3.EMIDate < t1.EMIDate
ORDER BY t1.InstNo;
Here's the O/P that I'm getting.
As of now, it works with a sample table's 1 Account and small data, but i tried it with table having more than 100 million data, and it is showing many duplicates.
The solution needs to check for each account, group by the accountno and display exact records where current InstNo's EMIDate is less than its previous record and it should display both previous record and next record as well

