InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 17:34:53 GMT20http://www.sqlservercentral.com/Forums/Topic1232229-392-1.aspx[quote][b]SQL Kiwi (1/10/2012)[/b]In fairness, you can do the same thing with a bit of UNION ALL action, but I like it.[/quote]Yeah, I like it too. Just bloody elegant it is.Todd FifieldWed, 11 Jan 2012 11:55:37 GMTtfifieldhttp://www.sqlservercentral.com/Forums/Topic1232229-392-1.aspx[quote][b]tfifield (1/10/2012)[/b][hr]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.[/quote]In fairness, you can do the same thing with a bit of UNION ALL action, but I like it.Tue, 10 Jan 2012 18:06:14 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1232229-392-1.aspxPaul,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 FifieldTue, 10 Jan 2012 17:55:31 GMTtfifieldhttp://www.sqlservercentral.com/Forums/Topic1232229-392-1.aspxI 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!Mon, 09 Jan 2012 05:10:06 GMTInsider-722249http://www.sqlservercentral.com/Forums/Topic1232229-392-1.aspxTables:[code="sql"]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))[/code]Test data:[code="sql"]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')[/code]Query:[code="sql"]SELECT T1.CustomerID, T1.AddressID, T1.EmailID, T1.FromDate, T1.ToDateFROM ( 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 T1WHERE T1.FromDate <= T1.ToDate[/code]Output:[img]http://www.sqlservercentral.com/Forums/Attachment10594.aspx[/img]Mon, 09 Jan 2012 03:29:48 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1232229-392-1.aspxThis 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.[code="sql"]BEGIN TRAN--Sample dataCREATE TABLE Customer (customerID INT IDENTITY PRIMARY KEY, Name VARCHAR(20))INSERT INTO CustomerSELECT 'Me'UNION ALL SELECT 'Myself'UNION ALL SELECT 'Irene'--Sample dataCREATE TABLE Addresses (addressesID INT IDENTITY PRIMARY KEY, customerID INT, addressLine1 VARCHAR(20), activeFrom DATE, activeTo DATE)INSERT INTO AddressesSELECT 1, '1 Short Street', '2000-01-01', '2005-12-31'UNION ALL SELECT 2, '100000 Long Street', '1985-01-26', NULLUNION ALL SELECT 3, '2 Short Street', '2005-12-30', NULLUNION ALL SELECT 1, '3 Short Street', '2006-01-01', NULL--Sample dataCREATE TABLE EmailAddresses (emailAddressesID INT IDENTITY PRIMARY KEY, customerID INT, emailAddress VARCHAR(100), activeFrom DATE, activeTo DATE)INSERT INTO EmailAddressesSELECT 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 toSELECT CONVERT(DATE,DATEADD(DAY,N,'1901-01-01')) AS NINTO TallyFROM (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) aALTER TABLE TallyALTER COLUMN N DATE NOT NULLALTER TABLE TallyADD CONSTRAINT PK_Tally_N PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100--Actual querySELECT cust.customerID, cust.Name, times.activeFrom, times.activeTo,times.addressLine1, times.emailAddressFROM Customer custLEFT 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.customerIDROLLBACK[/code]Mon, 09 Jan 2012 02:45:36 GMTCadavrehttp://www.sqlservercentral.com/Forums/Topic1232229-392-1.aspxHi, 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 [i]Customer[/i].This customer has one address but it has changed over the years so I have two records in table [i]Address[/i]:Addres1 active from 1/1/2000 until 31/12/2005Addres2 active from 1/1/2006 until nowThis customer also has an e-mail adress he changed it last year so I also have two records in table [i]Email[/i]:Email1 active from 1/1/2000 until 31/12/2011Email2 active from 1/1/2012 until nowWhat I want is to create a view that looks like this:[b]CUSTOMERID FROM TO ADDRESS E-MAIL[/b]1 1/01/2000 31/12/2005 Addres1 Email1 1 1/01/2006 31/12/2011 Addres2 Email1 1 1/01/2012 NULL Addres2 Email2Does anyone know how to achieve this in a performant way. Any hints are appreciated.Thanks!Mon, 09 Jan 2012 00:51:34 GMTInsider-722249