Two methods come to mind:
One is to identify the starts and ends of ranges via logic.
A start of a range is either a StartTs in Table1 where it’s not within another range (which handles overlapping ranges in Table1), or the day after an EndTs in Table2 which is in a Table1 range but not within another Table2 range.
An end of a range is either an EndTs in Table1 where it’s not within another range in either table, or the day before a StartTs in Table2 which is within a Table1 range but not within another a Table2 range.
So if this is correct, this logic should apply to identify all the Starts and Ends, and it should actually be complete because an End shouldn’t be able happen without a prior Start and vice-versa. If my logic is right.
This could be done with a series of
WHERE EXISTS / NOT EXISTS clauses.
The second method (and my preferred) is to transform the data into a list of in = 1, out = -1 events, so that a rolling sum can identify whether that date is in a Table1 range, and whether it’s in a Table2 range (probably put the day after EndTs values). Then, comparing LAG rows, it’s easy to identify the times that a range starts (current row in a Table1 range and not in a Table2 range, where the LAG row isn’t). Similarly for the ends of ranges, where the LAG row being in and the current row being not means the day before is the end of a range.
Both these methods would then need to be transformed into the right shape as requested, which could use LAG/LEAD.
My code for the second method, built up on a series of CTEs:
drop table if exists dbo.Table1;
create table dbo.Table1 (StartTS datetime, EndTS datetime);
insert Table1 values ('20150103','20150302')
insert Table1 values ('20150305','20150401')
drop table if exists dbo.Table2;
create table dbo.Table2 (StartTS datetime, EndTS datetime);
insert Table2 values ('20150107','20150227')
insert Table2 values ('20150301','20150313')
insert Table2 values ('20160101','20160102');
with dates as (
select StartTS as somedate, 1 as T1, 0 as T2 from dbo.Table1
select dateadd(day,1,EndTS), -1 as T1, 0 as T2 from dbo.Table1
select StartTS, 0 as T1, 1 as T2 from dbo.Table2
select dateadd(day,1,EndTS), 0 as T1, -1 as T2 from dbo.Table2
select distinct -- our rolling sums are by range, and we only want each date to appear once
, sum(T1) over (order by somedate range between unbounded preceding and current row) as T1
, sum(T2) over (order by somedate range between unbounded preceding and current row) as T2
, lag(T1,1,0) over (order by somedate) as PrevT1
, lag(T2,1,0) over (order by somedate) as PrevT2
when T1 > 0 and T2 = 0 and (PrevT1 = 0 or PrevT2 > 0) then 'InRange'
when (T1 = 0 or T2 > 0) and PrevT1 > 0 and PrevT2 = 0 then 'OutRange'
, Ranges as
select Somedate as StartTS, dateadd(day,-1,lead(Somedate) over (order by SomeDate)) as EndTs, RowType
where RowType is not null
select StartTS, EndTs
where RowType = 'InRange'
order by StartTS;