SQL Server 2016

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

drop table if exists #table1
drop table if exists #table2

create table #table1(CID int,PID int,SSC varchar(5),Start_date varchar(30),End_date varchar(30), Data varchar(20))
insert #table1
values
(1001,	1333,	'OP','20011505','19952012','OP2001156519952012'),
(1002,	1245,	'OR','20121005',NULL,'OR20121005'),
(1003,	1116,	'OP','20131215',NULL,'OP20131215'),
(1004,	1234,	'OP','20011505','19952012','OP2001156519952012')

create table #table2(CID int,PID int,SSC varchar(5),Start_date varchar(30),End_date varchar(30), Data varchar(20))
insert #table2
values
(1001,	1333,	'OP','20111015',NULL,'OP20111015'),
(1002,	1245,	'OR','20131005',NULL,'OR20131005'),
(1003,	1116,	'OP','20131415',NULL,'OP2013141520131516'),
(1004,	1235,	'OP','20121015',NULL,'OP20121015')

;with dataset as(
select 
	t1.* 
	,case when (t1.End_date is null and t2.End_date is null) then t1.data
		when  (t2.End_date is null or t1.End_date is null) then t1.data + ' - ' + t2.data 
		else null 
	end	AS DataConditions

from #table1 t1
	left join #table2 t2	on t1.CID = t2.CID
								AND t1.PID = t2.PID
								AND t1.SSC = t2.SSC

union all

select 
	t2.*
	,case when (t1.End_date is null and t2.End_date is null) then t1.data
		when  (t2.End_date is null or t1.End_date is null) then t1.data + ' - ' + t2.data 
		else null 
	end
		
from #table2 t2
	left join #table1 t1	on t1.CID = t2.CID
								AND t1.PID = t2.PID
								AND t1.SSC = t2.SSC

WHERE t1.PID IS NULL
)

select
	CID, PID, Coalesce(DataConditions, dataset.Data) AS SSC
from dataset

CID	PID	SSC
1001	1333	OP2001156519952012 - OP20111015
1002	1245	OR20121005
1003	1116	OP20131215
1004	1234	OP2001156519952012
1004	1235	OP20121015