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 |
| 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
