Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Combine different periods into one Expand / Collapse
Author
Message
Posted Monday, January 9, 2012 12:51 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Today @ 3:41 AM
Points: 2, Visits: 55
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:

CUSTOMERID FROM TO ADDRESS E-MAIL
1 1/01/2000 31/12/2005 Addres1 Email1
1 1/01/2006 31/12/2011 Addres2 Email1
1 1/01/2012 NULL Addres2 Email2

Does anyone know how to achieve this in a performant way. Any hints are appreciated.Thanks!
Post #1232229
Posted Monday, January 9, 2012 2:45 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 5:46 AM
Points: 2,443, Visits: 7,557
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




Not a DBA, just trying to learn

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/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1232273
Posted Monday, January 9, 2012 3:29 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 8:59 AM
Points: 11,194, Visits: 11,166
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:




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi


  Post Attachments 
Forum.png (110 views, 4.14 KB)
Post #1232295
Posted Monday, January 9, 2012 5:10 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Today @ 3:41 AM
Points: 2, Visits: 55
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!
Post #1232327
Posted Tuesday, January 10, 2012 5:55 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Saturday, August 30, 2014 9:13 PM
Points: 959, Visits: 2,885
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
Post #1233665
Posted Tuesday, January 10, 2012 6:06 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 8:59 AM
Points: 11,194, Visits: 11,166
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.




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1233667
Posted Wednesday, January 11, 2012 11:55 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Saturday, August 30, 2014 9:13 PM
Points: 959, Visits: 2,885
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
Post #1234258
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse