Merge statement on Historical data set

RajKumar 101 Reputation points
2021-09-10T03:16:01.63+00:00

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

130984-image.png

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,577 questions
{count} votes

Accepted answer
  1. EchoLiu-MSFT 14,576 Reputation points
    2021-09-16T08:18:53.41+00:00

    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:
    132674-image.png

    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.

    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Viorel 113.7K Reputation points
    2021-09-15T07:11:33.34+00:00

    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
    
    0 comments No comments