How to merge data of two tables

amit srivastava 21 Reputation points
2022-01-25T16:41:26.303+00:00

Hi Folks!!

I have one table as below

Table 1

EmpID Start_Date End_Date
1 10-01-2021 16-01-2021
1 25-01-2021 27-02-2021

Table 2

EmpID Start_Date End_Date
1 12-01-2021 14-01-2021
1 29-01-2021 28-03-2021

I need output as below

EmpID Start_Date End_Date
1 10-01-2021 12-01-2021
1 12-01-2021 14-01-2021
1 15-01-2021 16-01-2021
1 22-01-2021 22-01-2021
1 25-01-2021 29-01-2021
1 30-01-2021 27-02-2021
1 28-02-2021 28-03-2021

i.e to introduce new set of records for each date range.

Please suggest how to proceed that.

Thanks
Amit Srivastava

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,564 questions
{count} votes

1 answer

Sort by: Most helpful
  1. LiHong-MSFT 10,046 Reputation points
    2022-01-26T02:07:18.227+00:00

    Hi,@amit srivastava
    The following code is based on my own judgment, it may be different from the output you provided.Please comment below if you have any other requirements.

    create table #test1(EmpID int, Start_Date date,End_Date date);  
    create table #test2(EmpID int, Start_Date date,End_Date date);  
    insert into #test1 values(1 ,'1/10/2021', '1/16/2021'),(1, '1/25/2021' ,'2/27/2021');  
    insert into #test2 values(1 ,'1/12/2021', '1/14/2021'),(1, '1/29/2021','3/28/2021');  
      
    ;WITH CTE AS  
    (  
     select EmpID,All_Date,dateadd(day,1,All_Date) All_Date_add,  
            row_number()over(PARTITION BY EmpID ORDER BY All_Date) RNum  
     from( select * from #test1 union select * from #test2)a  
     unpivot(All_Date for datetype in([Start_Date],[End_Date]))u  
    )  
    SELECT A.EmpID,CASE WHEN A.RNum=1 THEN  A.All_Date ELSE A.All_Date_add END AS Start_Date,B.All_Date AS End_Date  
    FROM CTE A JOIN CTE B ON A.RNum=B.RNum-1  
    

    Output:
    168517-image.png

    Best regards,
    LiHong


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    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.