question

RajKumar-4323 avatar image
0 Votes"
RajKumar-4323 asked EchoLiu-msft edited

Merge statement on Historical data set

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


sql-server-transact-sql
image.png (8.2 KiB)
· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Sorry, your description is not clear enough for me. Please provide a minimal example that can illustrate all aspects of the problem (including CREATE and INSERT statements), and your expected output for the example (screenshots or excel are good)

1 Vote 1 ·

I showed the sample data as of today. Based on col1, col2, col3, and effectivefrom i need to calculate effectiveto and also update is_active to either 0 or 1.
For example if you look at record 1 and 2 the col3 changed from ga to fl incase i need effectiveto column value as 08/12/2019 and is_active first record to 0 and 2nd record to 1.

Hope this is clear.

0 Votes 0 ·

After comparing records 1 and 2, why do not you compare records 2 and 3?

0 Votes 0 ·
Show more comments
Viorel-1 avatar image
0 Votes"
Viorel-1 answered Viorel-1 edited

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




5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered EchoLiu-msft edited

Hi @RajKumar-4323

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.



image.png (7.9 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.