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.
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).
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.
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.