Help in Query of checking differences

Martin Kevin 241 Reputation points
2023-12-01T01:45:29.46+00:00

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

*/

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,265 questions
{count} votes

Accepted answer
  1. LiHongMSFT-4306 25,571 Reputation points
    2023-12-01T06:33:47.53+00:00

    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.


0 additional answers

Sort by: Most helpful