SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

drop table if exists dbo.SourceA;
drop table if exists dbo.SourceB;


create table dbo.SourceA
(
    BusinessKey int,
    StartDate   date,
    EndDate     date,
    Attribute   char(9)
);

create table dbo.SourceB
(
    BusinessKey int,
    StartDate   date,
    EndDate     date,
    Attribute   char(9)
);



insert dbo.SourceA(BusinessKey, StartDate, EndDate, Attribute)
values
    (1, '19990101', '19990113', 'black'),
    (1, '19990114', '19990313', 'red'),
    (1, '19990314', '19990513', 'blue'),
    (1, '19990514', '19990613', 'green'),
    (2, '20110714', '20110913', 'pink'),
    (2, '20110914', '20111113', 'white'),
    (2, '20111114', '20111213', 'gray');

insert dbo.SourceB(BusinessKey, StartDate, EndDate, Attribute)
values
    (1, '19990214', '19990313', 'left'),
    (1, '19990314', '19990413', 'right'),
    (1, '19990414', '19990713', 'centre'),
    (1, '19990714', '19990730', 'back'),
    (2, '20110814', '20110913', 'top'),
    (2, '20110914', '20111013', 'middle'),
    (2, '20111014', '20120113', 'bottom');
    
14 rows affected

select * from dbo.SourceB;


BusinessKey
StartDate
EndDate
Attribute


1
1999-02-14
1999-03-13
left

1
1999-03-14
1999-04-13
right

1
1999-04-14
1999-07-13
centre

1
1999-07-14
1999-07-30
back

2
2011-08-14
2011-09-13
top

2
2011-09-14
2011-10-13
middle

2
2011-10-14
2012-01-13
bottom


select a.businesskey
     , case when a.startdate > b.startdate then a.startdate else b.startdate end as startdate
     , case when a.enddate < b.enddate then a.enddate else b.enddate end as enddate
     , a.attribute as a_color
     , b.attribute as b_placement
from dbo.SourceA a 
join dbo.SourceB b 
    on a.businesskey = b.businesskey
    and (a.startdate between b.startdate and b.enddate 
         or b.startdate between a.startdate and a.enddate)
order by 1,2    


businesskey
startdate
enddate
a_color
b_placement


1
1999-02-14
1999-03-13
red
left

1
1999-03-14
1999-04-13
blue
right

1
1999-04-14
1999-05-13
blue
centre

1
1999-05-14
1999-06-13
green
centre

2
2011-08-14
2011-09-13
pink
top

2
2011-09-14
2011-10-13
white
middle

2
2011-10-14
2011-11-13
white
bottom

2
2011-11-14
2011-12-13
gray
bottom

BusinessKey	StartDate	EndDate	Attribute
1	1999-02-14	1999-03-13	left
1	1999-03-14	1999-04-13	right
1	1999-04-14	1999-07-13	centre
1	1999-07-14	1999-07-30	back
2	2011-08-14	2011-09-13	top
2	2011-09-14	2011-10-13	middle
2	2011-10-14	2012-01-13	bottom