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.

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.

table destination
employeeid fname lastname address area statdate enddate active
101 abc a b s 1/10/2019 31/12/2019 0
101 abc a x t 1/1/2020 31/07/2020 0
101 abc a y u 1/8/2020 31/12/9999 1
table source
id employeeid fname lname address area fromdate todate
101 abc a l nj 1/8/2013 31/10/2017
101 abc a k ny 1/11/2017 30/06/2018
101 abc a j chicago 1/7/2018 31/3/2020
101 abc a h dubai 1/4/2020 30/6/2020
101 abc aa g amsterdam 1/7/2020 30/11/2020
101 abc aa ppp france 1/12/2020
output
table destination
employeeid fname lastname address area statdate enddate active
101 abc a j chicago 1/10/2019 31/12/2019 0
101 abc aa g amsterdam 1/1/2020 31/07/2020 0
101 abc aa ppp france 1/8/2020 31/12/9999 1
table destination
employeeid fname lastname address area statdate enddate active
101 abc a b s 1/10/2019 31/12/2019 0
101 abc a x t 1/1/2020 31/07/2020 0
101 abc a y u 1/8/2020 31/12/9999 1
table source
id employeeid fname lname address area fromdate todate
101 abc a l nj 1/8/2013 31/10/2017
101 abc a k ny 1/11/2017 30/06/2018
101 abc a j chicago 1/7/2018 31/3/2020
101 abc a h dubai 1/4/2020 30/6/2020
101 abc aa g amsterdam 1/7/2020 30/11/2020
101 abc aa ppp france 1/12/2020
output
table destination
employeeid fname lastname address area statdate enddate active
101 abc a j chicago 1/10/2019 31/12/2019 0
101 abc aa g amsterdam 1/1/2020 31/07/2020 0
101 abc aa ppp france 1/8/2020 31/12/9999 1
For this type of question, we recommend that you post the CREATE TABLE statement for you table(s), INSERT statements with the sample data and then the desired result given the sample. We also recommend that you give a short description of the business rules that explains why you want that answer.
This makes it easy for us to copy and paste into a query window to develop a tested solution. Something we can't do from a screenshot.
As Erland already pointed out ...
While asking a question you need to provide a minimal reproducible example:
(1) DDL and sample data population, i.e. CREATE table(s) plus INSERT, T-SQL statements.
(2) What you need to do, i.e. logic, and your attempt implementation of it in T-SQL.
(3) Desired output based on the sample data in the #1 above.
(4) Your SQL Server version (SELECT @@version;)
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.
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
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.
16 people are following this question.