Disclaimer

This article does not pretend to present this solution as superior to others, it's intent is to show you how to solve the overlapping problem with an indexed view.

Problem

There is a schedule table that holds the start and the end of planned activities. We want to prevent users from scheduling activities that uses time already reserved.

Let’s create a tally table and the Schedule table with some data.

  CREATE DATABASE OverlappingEvents 
  GO
  USE OverlappingEvents 
  GO
  CREATE TABLE Numbers
      (
        Number INT NOT NULL
                   PRIMARY KEY
                   CHECK ( Number >= 1 )
      )
  GO
  INSERT INTO Numbers
          SELECT TOP ( 60 * 24 * 2 ) ROW_NUMBER() OVER ( ORDER BY c.object_id )
              FROM sys.columns c
                  CROSS JOIN sys.columns cc
  GO
  CREATE TABLE Schedule
      (
        PersonId INT NOT NULL
      , Activity VARCHAR(150) NOT NULL
      , StartDate DATETIME NOT NULL
      , EndDate DATETIME NOT NULL
      , CHECK ( EndDate > StartDate )
      )
  GO 
  INSERT INTO Schedule
      VALUES ( 1, 'Piano Concert', '2013-11-20 18:00', '2013-11-20 18:20' )
  INSERT INTO Schedule
      VALUES ( 1, 'Weeding Anniversary Dinner', '2013-12-09 19:00',
               '2013-12-09 23:30' )
  INSERT INTO Schedule
      VALUES ( 1, 'Work Meeting', '2013-12-09 18:00', '2013-12-09 19:15' )
  -- Oooops! it overlaps with the weeding anniversary
  INSERT INTO Schedule
      VALUES ( 2, 'Football Night', '2013-11-20 17:00', '2013-11-20 20:30' )
  INSERT INTO Schedule
      VALUES ( 2, 'Reading Club', '2013-12-09 19:00', '2013-12-09 20:30' )

If we review the activities we have scheduled, we will realize that this table is not a vey good friend, it did not prevent me from  appointing a work meeting that will make me arrive late to my anniversary (a wife is never happy with this).

Solution

How can we know if two activities overlap? Both use the same time period. In this case, the anniversary and the work meeting need the minutes 1 to 15 of the hour 19. Let’s create a view that will define which minutes are occupied by the activities.

  CREATE VIEW ScheduleTimeUsage
  WITH SCHEMABINDING
  AS
      SELECT s.PersonId
            , s.Activity
            , MinuteNeeded = DATEADD(MINUTE, n.Number - 1, s.StartDate)
          FROM dbo.Schedule s
              INNER JOIN dbo.Numbers n
              ON n.Number BETWEEN 1
                          AND     DATEDIFF(MINUTE, s.StartDate, s.EndDate)&bsp;
  GO
  SELECT *
      FROM ScheduleTimeUsage
      ORDER BY PersonId
        , MinuteNeeded
  GO

Please note that each activity has one row for each minute period it occupies. This was achieved by joining the Numbers table where the Number is less than the duration in minutes of the activity and adding this number to start date of the activity.

By focusing on the anniversary and meeting events, we will notice something interesting. The same time periods are present in both activities.  In other words, I’m using the same time for two different things.

  SELECT *
      FROM ScheduleTimeUsage
      WHERE MinuteNeeded BETWEEN '2013-12-09 19:10'
                         AND     '2013-12-09 19:15'
          AND PersonId = 1
      ORDER BY PersonId
        , MinuteNeeded

How can we prevent these duplicates from happening? Yes, you are right! A unique index on this view can do the trick. Let’s try it.

CREATE UNIQUE CLUSTERED INDEX 
IX_PreventActivityOverlapping 
ON ScheduleTimeUsage(PersonId,MinuteNeeded)
GO

Oops ! we can’t because of the meeting overlapping.  

We will remove it and try again.

  DELETE Schedule
      WHERE PersonId = 1
          AND Activity LIKE '%meeting%'

  CREATE UNIQUE CLUSTERED INDEX 
  IX_PreventActivityOverlapping 
  ON ScheduleTimeUsage(PersonId,MinuteNeeded)

We are good now. How does this works? It just enforces that the pair (PersonId,MinuteNeeded) does not exists more than once in the schedule table.

 Let’s see if this works as we expect. Try to insert other event that overlaps with the anniversary and other that does not.

  INSERT INTO Schedule
      VALUES ( 1, 'Mexico Soccer game', '2013-12-09 18:00', '2013-12-09 19:40' ) 
  -- No way Jose, this is trouble.
  INSERT INTO Schedule
      VALUES ( 1, 'Mexico Soccer game', '2013-12-09 18:00', '2013-12-09 18:40' ) 
  -- Not anymore =)

The first schedule was not accepted, but the second is totally ok.s example

The cinema shows example

Let’s create a movies table that contains the name and duration of each movie and a movie shows table in which we will allocate a movie in a room at the time it will start.

Our goal is to avoid shows overlapping based on the duration and start of a movie in a room. In other words, the next movie can't start until the current one is finished.

  CREATE TABLE Movies
      (
        MovieCode VARCHAR(10) NOT NULL
                              PRIMARY KEY
      , Name VARCHAR(50) NOT NULL
      , DurationInMinutes INT NOT NULL
                              CHECK ( DurationInMinutes > 0 )
      )
  GO
  INSERT INTO Movies
          SELECT 'G2013-1'
                , 'Gravity'
                , 90
          UNION ALL
          SELECT 'T2013-2'
                , 'Thor Dark World'
                , 110
          UNION ALL
          SELECT 'R2013-3'
                , 'Rush'
                , 128
          UNION ALL
          SELECT 'P2013-4'
                , 'Pulling Strings'
                , 90
  GO
  CREATE TABLE MovieShows
      (
        Room INT NOT NULL
                 CHECK ( Room > 0 )
      , MovieCode VARCHAR(10) NOT NULL
                              FOREIGN KEY REFERENCES Movies ( MovieCode )
      , StartHour INT NOT NULL
                      CHECK ( StartHour BETWEEN 0 AND 23 )
      , StartMinute INT NOT NULL
                        CHECK ( StartMinute BETWEEN 0 AND 59 )
                        DEFAULT 0
      )
  GO
  INSERT INTO MovieShows
          SELECT 1
                , 'G2013-1'
                , 18
                , 0
          UNION ALL
          SELECT 1
                , 'G2013-1'
                , 19
                , 40
          UNION ALL
          SELECT 2
                , 'T2013-2'
                , 17
                , 0
          UNION ALL
          SELECT 2
                , 'T2013-2'
                , 19
                , 0
          UNION ALL
          SELECT 3
                , 'R2013-3'
                , 16
                , 0
          UNION ALL
          SELECT 4
                , 'P2013-4'
                , 23
                , 50
  GO

See the contents of the tables and the overview of when a movie will start and end. 

  SELECT *
      FROM Movies
  SELECT *
      FROM MovieShows 
  SELECT s.Room
        , m.Name
        , m.DurationInMinutes
        , MovieStartMinuteOfDay = ( s.StartHour * 60 ) + s.StartMinute
        , MovieEndMinuteOfDay = ( ( s.StartHour * 60 ) + s.StartMinute
                                  + m.DurationInMinutes )
      FROM MovieShows s
          INNER JOIN Movies m
          ON m.MovieCode = s.MovieCode 
  GO

Applying the same principle of the personal schedule example, we will create a view that defines the time usage of the rooms.

  CREATE VIEW RoomsUsage
  WITH SCHEMABINDING
  AS
      SELECT s.Room
            , m.MovieCode
            , MinuteOfDayNeeded = ( ( s.StartHour * 60 ) + s.StartMinute
                                    + n.Number - 1 ) % ( 60 * 24 ) 
  -- A movie might last over midnight
          FROM dbo.MovieShows s
              INNER JOIN dbo.Movies m
              ON m.MovieCode = s.MovieCode
              INNER JOIN dbo.Numbers n
              ON n.Number BETWEEN 1 AND m.DurationInMinutes
  GO
  SELECT *
      FROM RoomsUsage
  GO

Create the unique index to prevent that a room allocates more than movie at the same time.

CREATE UNIQUE CLUSTERED INDEX 
IX_PreventOverlapping
ON RoomsUsage (Room,MinuteOfDayNeeded)
GO

If this works, it should not let us overlap movies in a room.

UPDATE Movies
    SET DurationInMinutes = 300
    WHERE MovieCode = 'G2013-1'
-- Not good, will cause overlaps
UPDATE Movies
    SET DurationInMinutes = 95
    WHERE MovieCode = 'G2013-1'
 -- Ok
INSERT INTO MovieShows
        SELECT 3
              , 'R2013-3'
              , 16
              , 50
-- Not good, will cause overlaps
INSERT INTO MovieShows
        SELECT 3
              , 'R2013-3'
              , 18
              , 30
 -- Ok
UPDATE MovieShows
    SET StartHour = 18
    WHERE Room = 2
        AND MovieCode = 'T2013-2'
        AND StartHour = 17 
--Not good, overlap
UPDATE MovieShows
    SET StartHour = 17
      , StartMinute = 10
    WHERE Room = 2
        AND MovieCode = 'T2013-2'
        AND StartHour = 17 
-- Great! It only allows good data.

What is interesting about this example is that this view enforces the business rule in both tables. We can’t increase the duration of a movie nor change its start time if this will cause that the movie needs time that is already reserved. 

Considerations

The numbers table size must be proportional to the duration of the events. The materialized view size is proportional to the quantity and duration of events. You can always set more precise time period by changing the time usage view to use other date parts.

This is a good tool to consider if using triggers is not an option for you.