Help in Query of checking differences

Question

Hi, I want to compare Round1 and Round2 column of current timeline with the very latest previous 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. Please check the query. Thanks

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 ('01','EE','1','A','2022-01-01', 600, 1,2)

Insert into #R Values ('01','EE','1','A','2023-01-01', 900, 1,2)

Insert into #R Values ('02','EE','1','A','2022-01-01', 600, 1,2)

Insert into #R Values ('02','EE','1','A','2023-01-01', 900, 1,2)

;WITH CTE AS 
( 
 SELECT *,ROW_NUMBER()OVER(PARTITION BY R1,R2,R3,R4 ORDER BY R5 DESC) AS RNum 
         ,LAG(Round1)OVER(PARTITION BY R1,R2,R3,R4 ORDER BY R5 DESC) AS Pre_Round1
 	     ,LAG(Round2)OVER(PARTITION BY R1,R2,R3,R4 ORDER BY R5 DESC) AS Pre_Round2 
 FROM #R 
) 
SELECT R1,R2,R3,R4,R5,Amount,Round1,Round2 
FROM CTE 
WHERE (Round1 <> Round2 AND RNum = 1) OR (Pre_Round1 <> Pre_Round2 AND RNum = 2) 
ORDER BY R1,R2,R3,R4,R5

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

/*

--Following are also getting in Expected results but should not because there is no difference in Round1 and Round2 in latest and previous timelines

01 EE 1 A 2022-01-01 600 1 2

01 EE 1 A 2023-01-01 900 1 2

02 EE 1 A 2022-01-01 600 1 2

02 EE 1 A 2023-01-01 900 1 2

*/

Answer 1

Hi @Martin Kevin

Try this query:

;WITH CTE1 AS
(
 SELECT *,ROW_NUMBER()OVER(PARTITION BY R1,R2,R3,R4 ORDER BY R5 DESC) AS RNum
 FROM #R
),CTE2 AS
(
 SELECT R1,R2,R3,R4,R5,Amount,Round1,Round2,RNum
 FROM CTE1 
 WHERE RNum = 1
 UNION ALL
 SELECT C1.R1,C1.R2,C1.R3,C1.R4,C1.R5,C1.Amount,C1.Round1,C1.Round2,C1.RNum
 FROM CTE2 C2 JOIN CTE1 C1 
   ON C2.R1=C1.R1 AND C2.R2=C1.R2 AND C2.R3=C1.R3 AND C2.R4=C1.R4 
                  AND C2.RNum + 1 = C1.RNum 
			      AND (C1.Round1 <> C2.Round1 OR C1.Round1 <> C2.Round1)
),CTE3 AS
(
 SELECT *,MAX(RNum)OVER(PARTITION BY R1,R2,R3,R4) AS Max_Rnum
 FROM CTE2
)
SELECT R1,R2,R3,R4,R5,Amount,Round1,Round2 
FROM CTE3
WHERE Max_Rnum <> 1

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.