Terrific! I was able to put this to use immediately. However, I needed to track temporal data by object (location and status in my case) so I added a piece identifier to solution 3 like so:

with Timestamps as
(
select PieceId, StartDate as DateVal from PieceLocation
union all select PieceId, coalesce(EndDate, '12/29/2029') from PieceLocation
union all select PieceId, StartDate from PieceStatus
union select PieceId, coalesce(EndDate, '12/29/2029') from PieceStatus
),
TimestampsRN as
(
select PieceId, DateVal, row_number() over (order by PieceId, DateVal) as Position
from Timestamps
),
Intervals as
(
select cur.PieceId, StartDate = cur.DateVal, EndDate = nxt.DateVal
from TimestampsRN cur
inner join TimestampsRN nxt on cur.PieceId = nxt.PieceId
and nxt.Position = cur.Position + 1
)
select i.StartDate, i.EndDate, i.PieceId, s.StatusId, l.LocationId
from Intervals i
left join PieceStatus s on i.PieceId = s.PieceId
and coalesce(s.EndDate, '12/31/2029') > i.StartDate
and s.StartDate < i.EndDate
left join PieceLocation l on i.PieceId = l.PieceId
and coalesce(l.EndDate, '12/31/2029') > i.StartDate
and l.StartDate < i.EndDate
go

-- I hope the formatting carries over into the comment. Here goes...

Vince10/17/2008 5:00:44 PM

-- Slightly different creation of the date ranges:
WITH StartDates AS ( -- Collect all "period delimiters"
SELECT StartDt FROM dbo.Phones
UNION SELECT StartDt FROM dbo.Addresses
),Ranges AS ( -- build the ranges from the collected delimiters
SELECT StartDt, (SELECT ISNULL(MIN(StartDt),99999999) EndDt FROM StartDates WHERE StartDT > StartDates1.StartDt) EndDt
FROM StartDates AS StartDates1
)
-- Join the rows to the ranges
SELECT P.Phone, A.Address, R.StartDt, R.EndDt
FROM Ranges R
LEFT JOIN Phones P ON NOT P.StartDt >= R.EndDt AND NOT ISNULL(P.EndDt,99999999) <= R.StartDt
LEFT JOIN Addresses A ON NOT A.StartDt >= R.EndDt AND NOT ISNULL(A.EndDt,99999999) <= R.StartDt

Cheers,
Chris

Christian10/8/2008 3:47:09 PM