Join temporal intervals with valid from and to intersections

join-temporal-interval.sql

declare @x table(xf date, xt date, name varchar(50))
declare @y table(yf date, yt date, name varchar(50))
declare @z table(zf date, zt date, name varchar(50))

insert @x values ('2000-01-01', '2000-01-05', 'X1')
insert @x values ('2000-01-06', '2000-01-20', 'X2')
insert @y values ('2000-01-01', '2000-01-10', 'Y1')
insert @y values ('2000-01-11', '2000-01-20', 'Y2')
insert @z values ('2000-01-01', '2000-01-15', 'Z1')
insert @z values ('2000-01-16', '2000-01-20', 'Z2')


select 
	t.f ValidFrom
	, LEAD(dateadd(d, -1, t.f), 1, '2154-12-31') OVER (ORDER BY t.f) ValidTo
	, *
from (
	select xf f from @x
	union 
	select yf f from @y
	union 
	select zf f from @z
) t
join @x x on t.f between x.xf and x.xt
join @y y on t.f between y.yf and y.yt
join @z z on t.f between z.zf and z.zt