Help in comparing data in last two timelines

Question

Help in comparing data in last two timelines

Mike 341

I have to compare data in last two timelines to make sure that what is the difference in data in last two timelines. The key field to join the data is P1, T1, O1, Edate. I want last two timelines with the data in the output result which has difference and all other data should be blank if no difference. Thanks

drop table #temp
Create table #temp (P1 char(10), T1 char(4), O1 char(4), Edate datetime, E1 char(2), B1 char(2), L1 char(2), BE1 char(2), Z1 char(2), AR char(2), Te1 char(2), VL1 numeric(6,2), MN1 numeric(6,2),
V1 numeric(6,2), V2 numeric(6,2))

Insert into #temp values ('01','DDD','11','2018-01-01','12','A1','D1','12','AB','A1','60',250, 10,200,300)
Insert into #temp values ('01','DDD','11','2021-01-01','12','A1','D1','12','AB','A1','60',350, 10,200,300)
Insert into #temp values ('01','DDD','11','2022-01-01','12','A1','D1','12','AB','A1','65',450, 10,200,300)

Insert into #temp values ('02','RRR','11','2018-01-01','12','A2','D1','44','BB','A1','60',250, 10,200,300)
Insert into #temp values ('02','RRR','11','2021-01-01','12','A2','D1','44','BB','A1','60',350, 10,200,300)
Insert into #temp values ('02','RRR','11','2022-01-01','12','A2','D1','44','BB','A1','65',450, 10,200,300)
Insert into #temp values ('02','RRR','11','2022-02-01','12','A2','D1','46','BB','A1','65',550, 10,200,400)

Insert into #temp values ('08','WEW','11','2020-01-01','12','A2','D1','44','BB','A1','60',250, 10,200,300)
Insert into #temp values ('08','WEW','11','2021-01-01','12','A2','D1','44','BB','A1','60',250, 10,200,300)
Insert into #temp values ('08','WEW','11','2022-01-01','14','A2','D1','44','CC','A1','60',250, 10,200,300)

Output is attached.

Accepted answer

0 additional answers

Your answer

Answer 1

Hi @Mike
Please check this query:

;WITH CTE AS  
(  
 SELECT *,ROW_NUMBER()OVER(PARTITION BY P1,T1,O1 ORDER BY Edate DESC) AS RNum  
 FROM #temp  
)  
SELECT A.P1,A.T1,A.O1,CAST(A.Edate AS DATE) AS Edate ,  
       CASE WHEN A.E1 = B.E1 THEN '' ELSE A.E1 END AS E1,  
    CASE WHEN A.B1 = B.B1 THEN '' ELSE A.B1 END AS B1,  
    CASE WHEN A.L1 = B.L1 THEN '' ELSE A.L1 END AS L1,  
    CASE WHEN A.BE1 = B.BE1 THEN '' ELSE A.BE1 END AS BE1,  
    CASE WHEN A.Z1 = B.Z1 THEN '' ELSE A.Z1 END AS Z1,  
    CASE WHEN A.AR = B.AR THEN '' ELSE A.AR END AS AR,  
    CASE WHEN A.Te1 = B.Te1 THEN '' ELSE A.Te1 END AS Te1,  
    CASE WHEN A.VL1 = B.VL1 THEN ''ELSE CAST(A.VL1 AS VARCHAR(20)) END AS VL1,  
    CASE WHEN A.MN1 = B.MN1 THEN '' ELSE CAST(A.MN1 AS VARCHAR(20)) END AS MN1,  
    CASE WHEN A.V1 = B.V1 THEN '' ELSE CAST(A.V1 AS VARCHAR(20)) END AS V1,  
    CASE WHEN A.V2 = B.V2 THEN '' ELSE CAST(A.V2 AS VARCHAR(20)) END AS V2  
FROM CTE A JOIN CTE B ON A.P1=B.P1 AND A.T1=B.T1 AND A.O1=B.O1  
WHERE (A.RNum =2 AND B.RNum =1) OR(A.RNum =1 AND B.RNum =2)

Output:

Best regards,
LiHong

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.

Mike 341 Reputation points

2022-02-16T13:38:43.39+00:00

Thanks @Mike . It works.

Share via

Help in comparing data in last two timelines

0 additional answers

Your answer