Date-Range multiple tables using T-SQL

Did you ever come across a situation where you need to join multiple tables (consisting FromDate and ToDate) and combine the data into single target table with proper date range?

In this blog post, I will try to explain how to date-range two source tables into single table based on FromDate and ToDate.

Problem: There are two tables Address and Email. Address of any customer can change at any point of time. Likewise, Email ID of customer can be changed at time. We need to track this information using FromDate (start time) and ToDate (end time).

Below are the sample data of these tables:

Address
AddressID	CustomerID	FullAddress	FromDate	ToDate
1	1	148th Ave NE, A10	2005-07-01	2006-08-07
2	1	156th Ave NE, D5	2006-08-08	2009-11-20
3	1	1100 NE 40th ST, A10	2009-11-21	2011-03-31
4	1	148th Ave NE, J101	2011-04-01	9999-12-31
5	2	14528 NE 40th ST	2007-05-01	2008-04-15
6	2	140th Ave NE, K11	2008-04-16	2012-06-25
7	2	145th Ave NE 31st ST	2012-06-26	9999-12-31
Email
EmailID	CustomerID	EmailAddress	FromDate	ToDate
1	1	hari11@gmail.com	2005-07-01	2010-09-30
2	1	hari21@yahoo.com	2010-10-01	2012-12-31
3	1	hari31@hotmail.com	2013-01-01	9999-12-31
4	2	john42@gmail.com	2007-05-01	2013-03-31
5	2	john52@hotmail.com	2013-04-01	9999-12-31

Now consider the above scenario and data, we need following output from these two tables:

Output
CustomerID	FullAddress	EmailAddress	FromDate	ToDate
1	148th Ave NE, A10	hari11@gmail.com	2005-07-01	2006-08-07
1	156th Ave NE, D5	hari11@gmail.com	2006-08-08	2009-11-20
1	1100 NE 40th ST, A10	hari11@gmail.com	2009-11-21	2010-09-30
1	1100 NE 40th ST, A10	hari21@yahoo.com	2010-10-01	2011-03-31
1	148th Ave NE, J101	hari21@yahoo.com	2011-04-01	2012-12-31
1	148th Ave NE, J101	hari31@hotmail.com	2013-01-01	9999-12-31
2	14528 NE 40th ST	john42@gmail.com	2007-05-01	2008-04-15
2	140th Ave NE, K11	john42@gmail.com	2008-04-16	2012-06-25
2	145th Ave NE 31st ST	john42@gmail.com	2012-06-26	2013-03-31
2	145th Ave NE 31st ST	john52@hotmail.com	2013-04-01	9999-12-31

Solution: There could be several ways to achieve the above output. Here is one the T-SQL scripts to get the desired output:
----------------------------------------------------------
--Description: Script to combine different date-ranged
--             columns into single date-ranged table.
----------------------------------------------------------
  
----------------------------------------------------------
--Create test tables
----------------------------------------------------------
IF OBJECT_ID('tempdb..#Address', 'U') IS NOT NULL DROP TABLE #Address
CREATE TABLE #Address
(
     AddressID INT IDENTITY(1, 1)
      ,CustomerID INT NOT NULL
      ,FullAddress VARCHAR(100) NOT NULL
      ,FromDate DATE NOT NULL
      ,ToDate DATE NOT NULL
      ,CONSTRAINT PKC_Address PRIMARY KEY CLUSTERED
   (CustomerID, FromDate)
)
  
IF OBJECT_ID('tempdb..#Email', 'U') IS NOT NULL DROP TABLE #Email
CREATE TABLE #Email
(
     EmailID INT IDENTITY(1, 1)
      ,CustomerID INT NOT NULL
      ,EmailAddress VARCHAR(50) NOT NULL
      ,FromDate DATE NOT NULL
      ,ToDate DATE NOT NULL
      ,CONSTRAINT PKC_Email PRIMARY KEY CLUSTERED
    (CustomerID, FromDate)
)
  
----------------------------------------------------------
--Insert test data
----------------------------------------------------------
INSERT #Address (CustomerID, FullAddress, FromDate, ToDate) VALUES
 (1, '148th Ave NE, A10', '2005-07-01', '2006-08-07')
,(1, '156th Ave NE, D5', '2006-08-08', '2009-11-20')
,(1, '1100 NE 40th ST, A10', '2009-11-21', '2011-03-31')
,(1, '148th Ave NE, J101', '2011-04-01', '9999-12-31')
,(2, '14528 NE 40th ST', '2007-05-01', '2008-04-15')
,(2, '140th Ave NE, K11', '2008-04-16', '2012-06-25')
,(2, '145th Ave NE 31st ST', '2012-06-26', '9999-12-31')

　
INSERT #Email (CustomerID, EmailAddress, FromDate, ToDate) VALUES
 (1, 'hari11@gmail.com', '2005-07-01', '2010-09-30')
,(1, 'hari21@yahoo.com', '2010-10-01', '2012-12-31')
,(1, 'hari31@hotmail.com', '2013-01-01', '9999-12-31')
,(2, 'john42@gmail.com', '2007-05-01', '2013-03-31')
,(2, 'john52@hotmail.com', '2013-04-01', '9999-12-31')

----------------------------------------------------------
--Verify test data
----------------------------------------------------------
SELECT * FROM #Address
SELECT * FROM #Email


----------------------------------------------------------
--T-SQL code to get the required output
----------------------------------------------------------
SELECT    t1.CustomerID
              ,t1.FullAddress
              ,t1.EmailAddress
              ,t1.FromDate
              ,t1.ToDate
FROM
(
SELECT    a.CustomerID
              ,a.FullAddress
              ,e.EmailAddress
              ,(SELECT MAX(U.FromDate)
           FROM (VALUES (a.FromDate), (e.FromDate)) AS U (FromDate)
                ) AS FromDate
               ,(SELECT MIN(U.ToDate)
           FROM (VALUES (a.ToDate), (e.ToDate)) AS U (ToDate)
                ) AS ToDate
FROM      #Address AS a
LEFT JOIN    #Email AS e
ON        a.CustomerID = e.CustomerID
) AS t1
WHERE ISNULL(t1.FromDate, '9999-12-31') <= ISNULL(t1.ToDate, '9999-12-31')

GO

OFFSET AND FETCH

SQL Server 2012 introduces brand new query hints that allow you to implement query paging solution. In the past, we have used TOP operator to return the top # number of rows from a table. However, OFFSET & FETCH query clauses can give you more benefits than just the TOP operator.

OFFSET: Allows you to offset the record pointer to a specific row in a table

FETCH: Allows you to fetch/return number of rows you request in Fetch.

OFFSET and FETCH when used together can return the exact/precise set of data you are looking for.

Let's take few examples to understand these features.

I am creating Employee as base table to use in my examples.

 

IF OBJECT_ID('dbo.Employee', 'U') IS NOT NULL DROP TABLE dbo.Employee
CREATE TABLE dbo.Employee
(
  EmployeeID INT NOT NULL
,EmployeeName NVARCHAR(100) NOT NULL
,BasicSalary INT NOT NULL
,DOB DATE NOT NULL
)
 

INSERT dbo.Employee (EmployeeID, EmployeeName, BasicSalary, DOB)
VALUES
(1, 'Hari', 5000, '1983-05-05')
,(2, 'John', 3500, '1978-10-25')
,(3, 'Peter', 4500, '1982-02-10')
,(4, 'Ravi', 2500, '1980-03-04')
,(5, 'Max', 3600, '1985-09-20')

SELECT EmployeeID
,EmployeeName
,BasicSalary
,DOB
FROM dbo.Employee

 







Use OFFSET to offset the record pointer to a specific row in a table:

SELECT EmployeeID
,EmployeeName
,BasicSalary
,DOB
FROM dbo.Employee
ORDER BY EmployeeID
OFFSET 2 ROWS

Use OFFSET to offset the record pointer to a specific row in a table and use FETCH to pull only required number of rows:

SELECT EmployeeID
,EmployeeName
,BasicSalary
,DOB
FROM dbo.Employee
ORDER BY EmployeeID
OFFSET 2 ROWS
FETCH NEXT 2 ROWS ONLY