correct dates based on id's

Question

Hi,
I have below data. I have to change the source to final result. My Date1 is messed up. Could you please help me with the query to achieve the Final result..

Answer 1

Check this statement:

;  
with Q1 as  
(  
    select *, row_number() over (order by UNQ_ID) i  
    from MessedTable  
),   
Q2 as  
(  
    select Q1.*, nd = case when p.Active_Flag = 0 then p.Date2 else '1/1/00' end  
    from Q1  
    left join Q1 p on p.i = Q1.i - 1  
)  
update Q2 set Date1 = nd  

Answer 2

Hi @Suman Gupta

You can try this query.

;with T1 as(  
  select *,row_number()over(order by ID2,UNQ_ID desc) as num from Source  
),T2 as(  
  select A.UNQ_ID,A.ID2,A.ID3,  
       case when B.Date2 = '12/31/99' or B.Date2 is null then A.Date1 else B.Date2 end as Date1,  
	   A.Date2,A.Active_Flg  
  from T1 as A left outer join T1 as B on A.num = B.num - 1)  
select * into Final_result from T2;  
select * from Final_result;  

Best regards,
Percy Tang

---

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.