How to merge data of two tables

Question

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

Answer 1

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:

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.