Modifying Contiguous Time Periods

This article explains how to modify contiguous time periods that were described in Joe Celko’s article ‘Contiguous Time Periods in SQL‘.  Joe describes the table itself that he calls the ‘Kuznetsov History Table’. He explains how it is used to store contiguous time intervals with constraint to ensure that the date periods really are contiguous, The editor suggested that I give a brief description of how to modify the data in the History table as this may not be entirely obvious.

When trusted constraints enforce data integrity, the data is guaranteed to be valid at the end of any statement, even if it is not committed. When we modify contiguous time periods, in order to get from one valid state to another we may need to insert a row and update another one, or we may need to delete a row and update another one. This is one of those cases when MERGE really shines – it allows us to get from one valid state to another in one statement, inserting, updating, and deleting rows as needed.

Prerequisites.

All we need is an empty table, as follows:

Some Easy Modifications.

It is easy to begin a new series of time periods

It is just as easy to continue adding periods to the end of the series.

Deleting one or more rows from the end is just as easy, and we shall skip the example. As we have seen, it is easy to perform typical, the most common operations against history of periods.

However, some other operations are less easy and need more explanations. Now that we have enough test data, let us move on to more complex examples. Here is the test data at this moment:

Adding periods to the beginning.

Each series of periods has exactly one first period – this is enforced by the following constraint: UNQ_Responsibilities_TaskId_PreviousFinishedAt.

As a result, when we are inserting one or more periods to the beginning of the series, we have to update the period that used to be the first before, as follows:

Now we will verify that our test data looks as expected, with a new row at the beginning, and PreviousFinishedAt column is modified to point to the new row for the row that used to be the first before this modification:

We are also going to discuss some other scenarios, such as adding/deleting periods in the middle of the series. In all these cases we shall be using MERGE, and the DML looks quite similar, so let us wrap it up in a stored procedure.

Creating a stored procedure

The following code implements this merging functionality with a stored procedure that uses a table valued parameter, as follows:

Let us use this stored procedure.

Filling a gap in the middle of the series

The following code fills the gap on November 25th.

Here is the data after this modification, with a period added in the middle fo the series:

Deleting a period in the middle of the series

The following code deletes the period added in the previous example.

Here is the data after this modification:

Inserting two periods in the middle, and adjusting an exaisting period to make room for them.

This is the last and most complex example involving our stored procedure:

Here is the data after running this script, with modifications in red rectangles:

Getting by on SQL Server 2005, without MERGE

We do not have MERGE on SQL Server 2005, so we have to use more complex ways to modify, such as delete and reinsert the whole series, or use more than one command to implement the change. The following operations are available:

• Inserting periods at the end
• Deleting periods at the end
• Updating periods from one valid state to another

For example, to delete the first period, we have to use an update to move it to the end, and then delete it, as follows:

Selects were added to the script so that we can see the intermediate and final state of the data. In the intermediate state, the first row is moved to the end, and the second one is updated to become the first:

In the final state, the row is gone:

We shall not re-implement all the previously discussed examples – that is left as an advanced exercise.

Good luck!