question

VishalParikh-3429 avatar image
0 Votes"
VishalParikh-3429 asked EchoLiu-msft answered

t-sql update query help

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.

93054-capture.jpg




sql-server-generalsql-server-transact-sqlazure-sql-database
capture.jpg (115.7 KiB)
· 5
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.

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
0 Votes 0 ·

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
0 Votes 0 ·

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.

0 Votes 0 ·

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;)

0 Votes 0 ·

...........

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

Hi @VishalParikh-3429,

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

Date and null cannot be compared, so I changed the null in the source table to '12/31/9999':
93322-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.
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.






image.png (7.9 KiB)
image.png (12.2 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.

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

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

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

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.

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.