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

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

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)
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.
After comparing records 1 and 2, why do not you compare records 2 and 3?
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
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:
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.
12 people are following this question.