THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Alexander Kuznetsov

Storing intervals of time with no overlaps.

I will demonstrate how use constraints to make sure that intervals of time have no overlaps. Also you may ensure that there are no gaps either, but if you choose to allow gaps, then the query to retrieve them is very easy. As discussed in my previous posts, you can also use triggers or UDFs wrapped in CHECK constraints, but only trusted constraints can guarantee that all your data is always 100% clean.

Here is the table and the first interval

 

CREATE TABLE dbo.IntegerSettings(SettingID INT NOT NULL,

  IntValue INT NOT NULL,

  StartedAt DATETIME NOT NULL,

  FinishedAt DATETIME NOT NULL,

  PreviousFinishedAt DATETIME NULL,

  CONSTRAINT PK_IntegerSettings_SettingID_FinishedAt PRIMARY KEY(SettingID, FinishedAt),

  CONSTRAINT UNQ_IntegerSettings_SettingID_PreviousFinishedAt UNIQUE(SettingID, PreviousFinishedAt),

  CONSTRAINT FK_IntegerSettings_SettingID_PreviousFinishedAt

    FOREIGN KEY(SettingID, PreviousFinishedAt)

    REFERENCES dbo.IntegerSettings(SettingID, FinishedAt),

  CONSTRAINT CHK_IntegerSettings_PreviousFinishedAt_NotAfter_StartedAt CHECK(PreviousFinishedAt <= StartedAt),

  CONSTRAINT CHK_IntegerSettings_StartedAt_Before_FinishedAt CHECK(StartedAt < FinishedAt)

);

GO

INSERT INTO dbo.IntegerSettings(SettingID, IntValue, StartedAt, FinishedAt, PreviousFinishedAt)

  VALUES(1, 1, '20070101', '20070103', NULL);

 

 

It has five constraints which work together to implement the business rule. Let me demonstrate how the more complex ones work. Of course, some constraints are simple and as such do not need any explanations.

 

There can be only one first interval for a setting

 

The constraint UNQ_IntegerSettings_SettingID_PreviousFinishedAt ensures exactly that. The first interval does not have a previous one, which means that PreviousFinishedAt IS NULL. The UNIQUE constraint  guarantees that there can be only one such row per setting. See for yourself:

INSERT INTO dbo.IntegerSettings(SettingID, IntValue, StartedAt, FinishedAt, PreviousFinishedAt)

  VALUES(1, 1, '20070104', '20070105', NULL);

/*

Server: Msg 2627, Level 14, State 2, Line 1

Violation of UNIQUE KEY constraint 'UNQ_IntegerSettings_SettingID_PreviousFinishedAt'. Cannot insert duplicate key in object 'dbo.IntegerSettings'.

The statement has been terminated.

*/

 

 

 

Next window must begin after the end of the previous one.

 

 

The constraint CHK_IntegerSettings_PreviousFinishedAt_NotAfter_StartedAt guarantees exactly that. See for yourself:

INSERT INTO dbo.IntegerSettings(SettingID, IntValue, StartedAt, FinishedAt, PreviousFinishedAt)

  VALUES(1, 2, '20070104', '20070109', '20070105')

/*

Server: Msg 547, Level 16, State 1, Line 1

INSERT statement conflicted with TABLE CHECK constraint 'CHK_IntegerSettings_PreviousFinishedAt_NotAfter_StartedAt'. The conflict occurred in database 'RiskCenter', table 'IntegerSettings'.

The statement has been terminated.

*/

 

 

Two different windows cannot refer to one and the same window as their previous one.

 

 

Again, the same constraint UNQ_IntegerSettings_SettingID_PreviousFinishedAt guarantees precisely that, as demonstrated below:

 

INSERT INTO dbo.IntegerSettings(SettingID, IntValue, StartedAt, FinishedAt, PreviousFinishedAt)

  VALUES(1, 3, '20070104', '20070115', '20070103')

 

Msg 2627, Level 14, State 1, Line 1

Violation of UNIQUE KEY constraint 'UNQ_IntegerSettings_SettingID_PreviousFinishedAt'. Cannot insert duplicate key in object 'dbo.IntegerSettings'.

The statement has been terminated.

 

This means that there can be no overlaps.

 

As you have seen, for every time window, there can be at most one preceding it, and at most one following it. The following interval cannot begin before the end of its previous one. Together these two statements mean that there can be no overlaps.

 

 

Working with gaps.

 

 

You can prohibit gaps altogether, just replace the following constraint:

 

  CONSTRAINT CHK_IntegerSettings_PreviousFinishedAt_NotAfter_StartedAt CHECK(PreviousFinishedAt <= StartedAt),

 

 

With a stricter one, as follows:

 

  CONSTRAINT CHK_IntegerSettings_PreviousFinishedAt_EqualTo_StartedAt CHECK(PreviousFinishedAt = StartedAt),

 

 

But if you allow gaps, the query to retrieve them is very simple and performant, as follows:

SELECT PreviousFinishedAt AS GapStart, StartedAt AS GapEnd

  FROM dbo.IntegerSettings

  WHERE StartedAt > PreviousFinishedAt;

 

This continues the series on denormalizing, which started with this post about working hours and appointments 


and continued with this post about running totals.
Published Sunday, March 08, 2009 8:12 PM by Alexander Kuznetsov

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Alejandro Mesa said:

Alex,

Very neat approach, without a doubt.

I haven't had time to play with it, and I wonder what would be like, if we need to update column [FinishedAt], due to a mistake, on the first or an intermediate row in the chain. I guess we will have, first, to delete any subsequent row in the chain, before beeing able to do that, if not, the constraint will stop us from doing the update, right?

If this is the case, then having deferred constraints, in SQL Server, will support this model even more.

Cheers,

AMB

March 9, 2009 12:51 PM
 

Alexander Kuznetsov said:

Hi Alejandro,

If you need to update column [FinishedAt], you have to also update PreviousFinishedAt for the next interval to match, as follows:

DECLARE @oldFinishedAt DATETIME, @newFinishedAt DATETIME;

SELECT @oldFinishedAt = '20070103', @newFinishedAt = '20070104';

UPDATE dbo.IntegerSettings

 SET FinishedAt = CASE

WHEN FinishedAt = @oldFinishedAt THEN @newFinishedAt

ELSE FinishedAt

   END,

 PreviousFinishedAt = CASE

WHEN PreviousFinishedAt = @oldFinishedAt THEN @newFinishedAt

ELSE PreviousFinishedAt

   END

WHERE SettingID = 1 AND (FinishedAt = @oldFinishedAt OR PreviousFinishedAt = @oldFinishedAt);

SELECT * FROM dbo.IntegerSettings;

Alternatively, you can use ON UPDATE CASCADE clause.

For more complex modifications, such as filling a gap or deleting an interval in the middle of a chain, MERGE is very handy. Prior to 2008 you can still do it without deleting and reinserting all the rows, but it is more tricky.

March 9, 2009 1:29 PM
 

Alejandro Mesa said:

Alex,

Wow, doable but not clean, and this is in case you need to update just one row and the row referencing it, how it would look like if we need to update more than 1 [FinishedAt], we will have to accomodate the change to be in one single UPDATE statement, if not it would fail.

We can not use CASCADE for self referencing constraints.

Having deferred constraints in SQL Server, will alow us to check the constraint after the transaction have had finished, so we would be able to use multiple statements.

Interesting the use of MERGE in SQL Server 2008, it could make it easy and all changes will be applied in one single transaction.

AMB

March 9, 2009 1:52 PM
 

Alexander Kuznetsov said:

Alejandro,

I agree that deferred constraints would allow for easier modifications. Thank you for pointing this out. Do you have a Connect item to vote for?

March 9, 2009 5:27 PM
 

Alejandro Mesa said:

March 9, 2009 9:22 PM
 

Alexander Kuznetsov said:

Suppose that you need to enforce the following business rule: contracts cannot be changed after you have

May 3, 2009 10:20 PM
 

Alexander Kuznetsov said:

Normal 0 false false false EN-US X-NONE X-NONE /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table

May 4, 2009 11:35 PM
 

Alexander Kuznetsov said:

Recently I encountered a case when I knew much more about the data than the optimizer. Originally the

July 7, 2009 9:00 PM
 

Henrik Staun Poulsen said:

Hi Alexander,

We have a datawarehouse, where we have a table like your IntegerSettings.

We need to populate this table with data from a source system where overlaps occur.

How do we do that?

If an overlap is found, we want the earliest starting date, and the latest ending date.

Best regards,

Henrik Staun Poulsen

August 25, 2009 5:36 AM
 

Alexander Kuznetsov said:

Hi Henrik,

This is a rather complex case. Are you running 2005 or 2008? Can you use CLR? I would very much rather load such data via a simple CLR cursor, than write complex SQL for it. What do you think?

August 27, 2009 10:55 PM
 

Henrik Staun Poulsen said:

Hi Alexander,

Yes, we're running SQL Server 2008. I'm trying to code this in SSIS, aka RBAR.

It is not easy, not even when considering one row at a time.

How would you calculate PreviousFinishedAt?

Best regards

Henrik

August 28, 2009 4:03 AM
 

Alexander Kuznetsov said:

Hi Henrik

I would run a cursor against the following:

SELECT * FROM (

SELECT StartedAt AS EventTime, 1 AS NumIntervalsChange FROM <your staging source>

UNION ALL

SELECT FinishedAt AS EventTime, -1 AS NumIntervalsChange FROM <your staging source>

) AS t

ORDER BY EventTime

When My running total changes from positive to 0, my interval has ended, so I can save it and assign a value to @PreviousFinishedAt. Next time an interval finishes, I can use @PreviousFinishedAt AND ASSIGN A NEW VALUE TO IT.

Does it make sense to you?

August 28, 2009 5:18 PM
 

Henrik Staun Poulsen said:

oHi Alexander,

Yes, it makes sense.

Only problem is that my staging source wants to insert a row in the middle of the existing fact table.

So I need to update one row, and insert one row.

In order to do that, I need the unique contraint to allow more than one (SettingID, PreviousFinishedAt), by adding in a small counter, as in (SettingID, PreviousFinishedAt, MyWorkingSmallInt).

When the load job finishes, MyWorkingSmallInt is set to zero.

I'll try to implement an example, if I can get it to work.

Best regards,

Henrik

August 31, 2009 8:00 AM
 

Alexander Kuznetsov said:

Henrik,

On 2008 one single MERGE takes care of inserting an interval right in the middle of a series. I should have written this up long ago...

August 31, 2009 5:35 PM
 

Alexander Kuznetsov said:

Henrik,

Sorry for the delay, I finally wrote up an example of modifications:

http://www.simple-talk.com/sql/t-sql-programming/modifying-contiguous-time-periods-in-a-history-table/

What do you think?

November 26, 2010 12:28 PM
 

Naomi said:

Hi Alexander,

Is there a way to prevent overlaps but without an extra column (PreviouslyFinishedAt)? I have StartSeat and EndSeat fields in the table and I want to ensure that LevelID, SectionID, Row, StartSeat/EndSeat combination doesn't have overlaps (can have gaps) within the same LevelID, SectionID, Row.

What will be the best way to achieve such functionality?

Thanks in advance.

March 26, 2012 1:00 PM
 

AlexK said:

Hi Naomi,

To my best knowledge, there is no way to prevent overlaps without the extra column PreviouslyFinishedAt.

April 5, 2012 3:11 PM

Leave a Comment

(required) 
(required) 
Submit

About Alexander Kuznetsov

Alex Kuznetsov has been working with object oriented languages, mostly C# and C++, as well as with databases for more than a decade. He has worked with Sybase, SQL Server, Oracle and DB2. He regularly blogs on sqlblog.com, mostly about database unit testing, defensive programming, and query optimization. Alex has written a book entitled "Defensive Database Programming with Transact-SQL" and several articles on simple-talk.com and devx.com. Currently he works at DRW Trading Group in Chicago, where he leads a team of developers, practicing agile development, defensive programming, TDD, and database unit testing.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement