t-sql update query help

Question

i want t-sql update query.

i have source table and destination table.i want to update destination table based on comparing of source and destination table enddate.

attached source tables and expected output table.

Answer 1

Hi @Vishal Parikh ,

Please refer to:

create table source(employeeid int,fname char(15),lname char(15)  
,address char(15),area char(15), fromdate date,todate date)  
insert into source values(101,'abc','a','l','nj','1/8/2013','10/31/2017'),  
(101,'abc','a','k','ny','1/11/2017','06/30/2018'),  
(101,'abc','a','j','chicago','1/7/2018','3/31/2020'),  
(101,'abc','a','h','dubai','1/4/2020','6/30/2020'),  
(101,'abc','aa','g','amsterdam','1/7/2020','11/30/2020'),  
(101,'abc','aa','ppp','france','1/12/2020','12/31/9999')  
					  
create table destination(employeeid int,fname char(15),lastname char(15)  
,address char(15),area char(15), statdate date,enddate date,active int)  
insert into destination values(101,'abc','a','b ','s','1/10/2019','12/31/2019',0)  
,(101,'abc','a','x ' ,'t','1/1/2020','07/31/2020',0)  
,(101,'abc','a','y','u','1/8/2020','12/31/9999',1)  
  
update d  
set d.lastname=s.lname,  
    d.address=s.address,  
	d.area=s.area  
from destination d  
JOIN source s ON d.enddate BETWEEN s.fromdate AND s.todate  
  
select * from destination  
  
drop table source  
drop table destination  

Output:

Date and null cannot be compared, so I changed the null in the source table to '12/31/9999':

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.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Does the following method work?Could you have any updates?Please also remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.

Echo

Answer 3

Could you please validate all the answers so far and provide any update?
If all of them are not working or helpful, please provide more sample data or details about your issue.
Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.