Created
June 8, 2022 22:37
-
-
Save FlameWert/c4c2dd94d63dc24e6a052b67c79e15b0 to your computer and use it in GitHub Desktop.
Join two tables with date ranges
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- 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') |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment