Sunday, February 10, 2013

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