# Prevent overlapping of time events with an indexed view

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

### Resources:

OverLapping2.sql
Total article views: 3830 | Views in the last 30 days: 1

Related Articles
FORUM

### Blocking Issue:Insert blocking select statements

Insert blocking select statements

FORUM

### Insert - Exec Select statements

Insert - Exec Select statements

SCRIPT

### AUTOGENERATE INSERT UPDATE SELECT STATEMENTS

use metadata to generate basic create procedure statements for insert update and select

FORUM

### DYNAMIC SELECT & INSERT QUERY HELP

DYNAMIC SELECT & INSERT QUERY HELP

FORUM

### SQL 2000 Reporting Services - "Select Schedule Issue"

No windows appears after selecting Schedule option

Tags
 indexed views integrity overlap time events