Merge statement on Historical data set

Question

Hi All,

attached sample table. Here I need sql query to find the effectiveto column from efectivefrom column.

Please provide the merge statement to calculate type 2 from col1,col2,col3 columns

Answer 1

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  

Output:

If you have any question, please feel free to let me know.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 2

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