The first step in my solution defining the CTE Timestamps is almost identical to Davide’s Timestamps CTE, except that mine uses a UNION operation as the last set operation instead of UNION ALL. This means that duplicate timestamps are removed at this step in my solution.
In the second step (in my solution, defining CTE TSRN—timestamps with row numbers), I didn’t need to use the combination of the DENSE_RANK function and the DISTINCT clause. Instead, I simply assigned row numbers to position the timestamps chronologically.
The third step in my solution (i.e., defining the Intervals CTE) is identical to Davide’s. Then our solutions start to differ. My solution reaches the last step at this point, implemented by the outer query. The outer query joins the Intervals CTE, which contains all distinct possible periods with each of the attribute tables (Phones and Addresses, in our case) containing validity periods, to match all overlapping validity periods to each possible period. A LEFT OUTER JOIN is used because there’s no guarantee that each possible period will find a match in each attribute table.
Maintaining the Solutions
As you can see from this problem, treatment of time periods can be challenging and quite tricky—especially because SQL Server has incomplete support for temporal data. If you want to use Davide’s or my solution to support new attributes, you’ll need to create a new table for each attribute, then revise the solution to include the attributes. For either solution, you’ll need to revise the Timestamps CTE, adding the timestamps from the new attribute table. For Davide’s solution, you’ll need to revise the UnifiedValues CTE, adding a UNION ALL set operation and a query against the new attribute table to pull the validity periods of the new attribute. In addition, you’ll need to add the new attribute name to the IN clause of the PIVOT operator in the outer query. For my solution, you’ll need to revise the outer query, adding a LEFT OUTER JOIN to the new attribute table.
Did you manage to come up with a different solution than mine or Davide’s? If so, share your solution by posting a comment to this article.