Hi @RajKumar ，
Please also check:
CREATE TABLE #test([key] INT,col1 VARCHAR(25),col2 INT,col3 VARCHAR(25), effectiveFrom DATE,effectiveTo DATE,is_active INT) INSERT INTO #test VALUES (1,'sam',500,'GA','1/15/2019','1/1/9999',null), (2,'sam',500,'FL','8/13/2019','1/1/9999',null), (3,'keith',100,'CA','2/14/2019','1/1/9999',null), (4,'keith',200,'CA','4/18/2019','1/1/9999',null) ;WITH cte as(SELECT *,RANK() OVER(PARTITION BY col1 order by effectiveFrom,col2,col3) rr FROM #test) UPDATE c SET c.effectiveTo=ISNULL(DATEADD(day,-1,c2.effectiveFrom),c.effectiveTo), c.is_active=CASE WHEN c.rr=1 THEN 0 ELSE 1 END FROM cte c LEFT JOIN cte c2 ON c.col1=c2.col1 AND c.rr=c2.rr-1 SELECT * FROM #test
If you have any question, please feel free to let me know.
If the answer is helpful, please click "Accept Answer" and upvote it.
Try this statement:
update t set effectiveTo = case when ld is not null then dateadd(day, -1, ld) else '9999-01-01' end, is_active = case when ld is null then 1 else 0 end from ( select *, lead(effectiveFrom) over (partition by col1 order by effectiveFrom) ld from MyTable ) t