Combine different periods into one
-
Hi, I want to combine different periods into one, I don't know how to explain it properly, so I try to explain it by an example:
I have a customer (ID:1) that is active from 1/1/2000 until now in table Customer.
This customer has one address but it has changed over the years so I have two records in table Address:
Addres1 active from 1/1/2000 until 31/12/2005
Addres2 active from 1/1/2006 until now
This customer also has an e-mail adress he changed it last year so I also have two records in table Email:
Email1 active from 1/1/2000 until 31/12/2011
Email2 active from 1/1/2012 until now
What I want is to create a view that looks like this:
CUSTOMERIDFROM TO ADDRESS E-MAIL
1 1/01/200031/12/2005 Addres1 Email1
1 1/01/200631/12/2011 Addres2 Email1
1 1/01/2012NULL Addres2 Email2
Does anyone know how to achieve this in a performant way. Any hints are appreciated.Thanks!
-
This should get you on the right sort of track. Bear in mind that I've knocked it together very quickly and am fairly certain that there's a bug in the activeFrom and activeTo dates, so make sure to examine carefully.
BEGIN TRAN--Sample data
CREATE TABLE Customer (customerID INT IDENTITY PRIMARY KEY, Name VARCHAR(20))
INSERT INTO Customer
SELECT 'Me'
UNION ALL SELECT 'Myself'
UNION ALL SELECT 'Irene'
--Sample data
CREATE TABLE Addresses (addressesID INT IDENTITY PRIMARY KEY, customerID INT,
addressLine1 VARCHAR(20), activeFrom DATE, activeTo DATE)
INSERT INTO Addresses
SELECT 1, '1 Short Street', '2000-01-01', '2005-12-31'
UNION ALL SELECT 2, '100000 Long Street', '1985-01-26', NULL
UNION ALL SELECT 3, '2 Short Street', '2005-12-30', NULL
UNION ALL SELECT 1, '3 Short Street', '2006-01-01', NULL
--Sample data
CREATE TABLE EmailAddresses (emailAddressesID INT IDENTITY PRIMARY KEY, customerID INT,
emailAddress VARCHAR(100), activeFrom DATE, activeTo DATE)
INSERT INTO EmailAddresses
SELECT 1, 'Me.2k@hotmail.com', '2000-01-01', '2011-12-31'
UNION ALL SELECT 1, 'hotmaillSucks.Me.2k12@gmail.com', '2012-01-01', NULL
--Required to get the dates from and to
SELECT CONVERT(DATE,DATEADD(DAY,N,'1901-01-01')) AS N
INTO Tally
FROM (SELECT TOP 1000000 ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS N
FROM master.dbo.syscolumns sc1, master.dbo.syscolumns sc2, master.dbo.syscolumns sc3) a
ALTER TABLE Tally
ALTER COLUMN N DATE NOT NULL
ALTER TABLE Tally
ADD CONSTRAINT PK_Tally_N
PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100
--Actual query
SELECT cust.customerID, cust.Name, times.activeFrom, times.activeTo,
times.addressLine1, times.emailAddress
FROM Customer cust
LEFT OUTER JOIN (SELECT addresses.customerID,
MAX(CASE WHEN ISNULL(addresses.activeFrom,GETDATE()) >= ISNULL(emails.activeFrom,GETDATE())
THEN addresses.activeFrom
ELSE ISNULL(emails.activeFrom,addresses.activeFrom) END) AS activeFrom,
MAX(CASE WHEN ISNULL(addresses.activeTo,GETDATE()) <= ISNULL(emails.activeTo,GETDATE())
THEN addresses.activeTo
ELSE emails.activeTo END) AS activeTo,
addresses.addressLine1, emails.emailAddress
FROM (SELECT addressesID, customerID, addressLine1, activeFrom, activeTo, N
FROM Addresses
CROSS APPLY Tally
WHERE activeFrom <= N AND (ISNULL(activeTo,GETDATE()) >= N)) addresses
LEFT OUTER JOIN (SELECT emailAddressesID, customerID, emailAddress, activeFrom, activeTo, N
FROM EmailAddresses
CROSS APPLY Tally
WHERE activeFrom <= N AND (ISNULL(activeTo,GETDATE()) >= N)) emails ON addresses.N = emails.N
AND addresses.customerID = emails.customerID
GROUP BY addresses.addressLine1, emails.emailAddress, addresses.customerID) times ON cust.customerID = times.customerID
ROLLBACK
Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/ -
Tables:
DECLARE @Customer TABLE
(
CustomerID integer PRIMARY KEY
)
DECLARE @Address TABLE
(
AddressID integer PRIMARY KEY NONCLUSTERED,
CustomerID integer NOT NULL,
FromDate date NOT NULL,
ToDate date NOT NULL,
UNIQUE CLUSTERED (CustomerID, AddressID)
)
DECLARE @Email TABLE
(
EmailID int PRIMARY KEY NONCLUSTERED,
CustomerID integer NOT NULL,
FromDate date NOT NULL,
ToDate date NOT NULL,
UNIQUE CLUSTERED (CustomerID, EmailID)
)
Test data:
INSERT @Customer
(CustomerID)
VALUES
(1)
INSERT @Address
(AddressID, CustomerID, FromDate, ToDate)
VALUES
(1, 1, '2000-01-01', '2005-12-31'),
(2, 1, '2006-01-01', '9999-12-31')
INSERT @Email
(EmailID, CustomerID, FromDate, ToDate)
VALUES
(1, 1, '2000-01-01', '2005-12-31'),
(2, 1, '2006-01-01', '2011-12-31'),
(3, 1, '2012-01-01', '9999-12-31')
Query:
SELECT
T1.CustomerID,
T1.AddressID,
T1.EmailID,
T1.FromDate,
T1.ToDate
FROM
(
SELECT
c.CustomerID,
a.AddressID,
e.EmailID,
(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 @Customer AS c
LEFT JOIN @Address AS a ON
a.CustomerID = c.CustomerID
LEFT JOIN @Email AS e ON
e.CustomerID = c.CustomerID
) AS T1
WHERE
T1.FromDate <= T1.ToDate
Output:
-
I tried both solutions and decided to go for Kiwi's solution because it gives me a better performance.
Thank you both for you're effort and the quick reply!
-
Paul,
I would never have considered using actual columns from a table in a VALUES clause. Great little nugget there! I guess I need to think more outside the box.
Todd Fifield
-
tfifield (1/10/2012)
I would never have considered using actual columns from a table in a VALUES clause. Great little nugget there! I guess I need to think more outside the box.In fairness, you can do the same thing with a bit of UNION ALL action, but I like it.
-
SQL Kiwi (1/10/2012)
In fairness, you can do the same thing with a bit of UNION ALL action, but I like it.
Yeah, I like it too. Just bloody elegant it is.
Todd Fifield
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply