Hi, I want to compare Round1 and Round2 column of current timeline with the very latest last timeline. Round1 and Round2 of latest timeline should not be different from very last timeline. The key column of this table is R1,R2,R3,R4,R5.
Create table #R (R1 char(10), R2 char(10), R3 char(10), R4 char(10), R5 date, Amount int, Round1 int, Round2 int)
Insert into #R Values ('AA','BB','CC','A','2020-01-01', 700, 2,2)
Insert into #R Values ('AA','BB','CC','A','2021-01-01', 900, 2,2)
Insert into #R Values ('AA','BB','CC','A','2022-01-01', 700, 2,2)
Insert into #R Values ('AA','BB','CC','A','2023-01-01', 800, 1,2)
Insert into #R Values ('AA','DD','CC','A','2022-01-01', 600, 1,1)
Insert into #R Values ('AA','DD','CC','A','2023-01-01', 900, 1,1)
Insert into #R Values ('EE','AA','CC','A','2022-01-01', 600, 1,1)
Insert into #R Values ('EE','AA','CC','A','2023-01-01', 900, 1,2)
Insert into #R Values ('EE','AA','RR','A','2022-01-01', 500, 1,1)
Insert into #R Values ('EE','AA','RR','A','2023-01-01', 900, 1,1)
Insert into #R Values ('EE','AA','RR','A','2024-01-01', 980, 1,1)
Expected Result:
R1 R2 R3 R4 R5 Amount Round1 Round2
AA BB CC A 2022-01-01 700 2 2
AA BB CC A 2023-01-01 700 1 2 -- Round2 is different from previous timeline
EE AA CC A 2022-01-01 600 1 1
EE AA CC A 2023-01-01 900 1 2 -- Round2 is different from previous timeline
