Join two tables with date ranges

2DateRanges

-- Create schema
CREATE SCHEMA Experiment;

-- Create first table
CREATE TABLE [Experiment].[DateRange1](
	[EmployeeID] [INT] NULL,
	[Name] [VARCHAR](50) NULL,
	[StartDate] [DATETIME] NULL,
	[EndDate] [DATETIME] NULL
) ON [PRIMARY]
GO

-- Create second table
CREATE TABLE [Experiment].[DateRange2](
	[JobID] [int] NULL,
	[JobName] [varchar](50) NULL,
	[EmployeeID] [int] NULL,
	[StartDate] [datetime] NULL,
	[EndDate] [datetime] NULL
) ON [PRIMARY]
GO

-- Insert data into first table
INSERT [Experiment].[DateRange1] ([EmployeeID], [Name], [StartDate], [EndDate]) VALUES (1, N'Merin Nakarmi', CAST(N'1983-06-07T00:00:00.000' AS DateTime), CAST(N'2022-02-14T00:00:00.000' AS DateTime))
GO
INSERT [Experiment].[DateRange1] ([EmployeeID], [Name], [StartDate], [EndDate]) VALUES (1, N'Mr. Merin Nakarmi', CAST(N'2022-02-14T00:00:00.000' AS DateTime), NULL)
GO
INSERT [Experiment].[DateRange1] ([EmployeeID], [Name], [StartDate], [EndDate]) VALUES (2, N'Manisha Rai', CAST(N'1992-05-25T00:00:00.000' AS DateTime), CAST(N'2022-02-14T00:00:00.000' AS DateTime))
GO
INSERT [Experiment].[DateRange1] ([EmployeeID], [Name], [StartDate], [EndDate]) VALUES (2, N'Mrs. Manisha Rai', CAST(N'2022-02-14T00:00:00.000' AS DateTime), NULL)
GO

-- Insert data into second table
INSERT [Experiment].[DateRange2] ([JobID], [JobName], [EmployeeID], [StartDate], [EndDate]) VALUES (1, N'Teacher', 1, CAST(N'2006-06-01T00:00:00.000' AS DateTime), CAST(N'2007-06-30T00:00:00.000' AS DateTime))
GO
INSERT [Experiment].[DateRange2] ([JobID], [JobName], [EmployeeID], [StartDate], [EndDate]) VALUES (2, N'Clerk', 1, CAST(N'2008-03-17T00:00:00.000' AS DateTime), CAST(N'2010-05-15T00:00:00.000' AS DateTime))
GO
INSERT [Experiment].[DateRange2] ([JobID], [JobName], [EmployeeID], [StartDate], [EndDate]) VALUES (3, N'Sinet Developer', 1, CAST(N'2010-12-13T00:00:00.000' AS DateTime), CAST(N'2013-06-06T00:00:00.000' AS DateTime))
GO
INSERT [Experiment].[DateRange2] ([JobID], [JobName], [EmployeeID], [StartDate], [EndDate]) VALUES (4, N'SacCounty Developer', 1, CAST(N'2013-07-13T00:00:00.000' AS DateTime), CAST(N'2016-09-27T00:00:00.000' AS DateTime))
GO
INSERT [Experiment].[DateRange2] ([JobID], [JobName], [EmployeeID], [StartDate], [EndDate]) VALUES (5, N'SacCounty Developer', 1, CAST(N'2017-10-16T00:00:00.000' AS DateTime), NULL)
GO
INSERT [Experiment].[DateRange2] ([JobID], [JobName], [EmployeeID], [StartDate], [EndDate]) VALUES (6, N'Kintergarden Teacher', 2, CAST(N'2014-05-17T00:00:00.000' AS DateTime), CAST(N'2016-06-30T00:00:00.000' AS DateTime))
GO
INSERT [Experiment].[DateRange2] ([JobID], [JobName], [EmployeeID], [StartDate], [EndDate]) VALUES (7, N'Sailabha Clerk', 2, CAST(N'2017-09-25T00:00:00.000' AS DateTime), CAST(N'2019-03-15T00:00:00.000' AS DateTime))
GO
INSERT [Experiment].[DateRange2] ([JobID], [JobName], [EmployeeID], [StartDate], [EndDate]) VALUES (8, N'IME General Agent', 2, CAST(N'2019-04-25T00:00:00.000' AS DateTime), NULL)
GO


-- Query

WITH 
DateCollection AS
(
SELECT EmployeeID, StartDate AS Date FROM Experiment.Daterange1
UNION 
SELECT EmployeeID, EndDate FROM Experiment.Daterange1
UNION 
SELECT EmployeeID, StartDate FROM Experiment.Daterange2
UNION
SELECT EmployeeID, Enddate FROM Experiment.Daterange2
)
,
DateRanges AS
(
SELECT EmployeeID, Date AS StartDate, LEAD(Date) OVER(PARTITION BY EmployeeID ORDER BY Date) AS EndDate FROM DateCollection 
)
,
ValidDateRanges AS
(
SELECT * FROM DateRanges WHERE StartDate IS NOT NULL
)

SELECT B.Name, C.JobName, A.StartDate, A.EndDate FROM ValidDateRanges A
	LEFT JOIN Experiment.DateRange1 B ON A.EmployeeID = B.EmployeeID AND B.StartDate <= A.StartDate AND A.StartDate < ISNULL(B.EndDate, '9999-12-31')
	LEFT JOIN Experiment.DateRange2 C ON A.EmployeeID = C.EmployeeID AND C.StartDate <= A.StartDate AND A.StartDate < ISNULL(C.EndDate, '9999-12-31')