I need to find whether a new schedule overlaps any existing schedules.

This is the "intervals" table:

     id  first              last    
     1   1900-01-01 09:00   1900-01-01 10:00
     2   1900-01-01 15:00   1900-01-01 18:00
     3   1900-01-01 18:01   1900-01-01 08:00

I am using a scalar function dbo.TimeOnly for extracting time part from the datetime fields.

My selection criteria as follows

First case

     declare @start datetime
     declare @end datetime

     set @start = dbo.TimeOnly('2011-may-11 08:01:00');
     set @end = dbo.TimeOnly('2011-may-11 15:30:00');

     select  * from intervals where 
     ( NOT ( dbo.TimeOnly(last) < @start OR  @end < dbo.TimeOnly(first) ) )

This will return 1st and 2nd records. I got this logic from Check whether the schedule overlaps each other?

Second case

    set @start = dbo.TimeOnly('2011-may-11 07:01:00');
    set @end = dbo.TimeOnly('2011-may-11 08:30:00');

How do I write a query that will return only the 3rd record for the criteria in the second case?

UPDATE

I will give more details for my problem

Different people are managing a particular event for a certain time duration in a day.

For Monday, the schedule format is like this

    Id  Start       End          User   Days    
     1  00:01 AM    08:00 AM     'A'    1
     2  08:01 AM    04:00 PM     'B'    1
     3  04:00 PM    00:00 AM     'C'    1

For Tuesday's

     4  08:01 AM    04:00 PM     'B'    2
     5  07:00 PM    07:00 AM     'C'    2

For Wednesday's

     6  08:01 AM    04:00 PM     'A'    4
     7  10:00 PM    08:00 AM     'B'    4

Here days are stored in the bit value format ie

Monday=1,Tuesday=2,Wednesday=4,Thursday=8开发者_运维技巧,Friday=16,Saturday=32 and Sunday=64

When we creating a schedule for a particular day, it should not overlap between times. I would like to get a SQL query for checking any schedules exists while creating a new schedule for a particular day.

For a particular event time (Say An even occured at 04:00 AM on Tuesday) I would like to find the correct schedule (Will be "5") that falls between the Start and End time.

Change your SELECT to this:

select  * from intervals where 
(
( dbo.TimeOnly(last) > dbo.TimeOnly(first)
AND
NOT (dbo.TimeOnly(last) < @start OR  @end < dbo.TimeOnly(first)) )

OR

( dbo.TimeOnly(last) < dbo.TimeOnly(first)
AND
( @start >= dbo.TimeOnly(first) OR @end <= dbo.TimeOnly(last) OR (@start < dbo.TimeOnly(first) AND @end > dbo.TimeOnly(last)) ) )
)

I might've missed a parenthesis somewhere, but I hope not.

The concept here is a query with 2 main groupings combined with an OR. The first clause checks intervals where last > first and is mainly a copy of your existing query with the addition of the last > first condition, while the 2nd clause checks intervals where last < first.

In the case where last < first, there are 3 ways that an interval can overlap:

• start is after the interval's first
• end is before the interval's last
• start and end completely engulf the interval, i.e., start is before first and end is after last

Any one of these 3 conditions would mean the schedule to check is within an existing interval, so the 3 conditions are combined with ORs.

You will need to do something like this:

Declare @start datetime
Declare @duration int

Set @start = dbo.TimeOnly('2011-May-11 07:01:00');
-- Get the number of minutes in your timespan
Set @duration = DateDiff(minute, '2011-May-11 07:01:00', '2011-May-12 08:30:00');

Select id, first, last
From intervals
Where (DateDiff(minute, dbo.TimeOnly(first)), @start) + @duration) > 0
AND DateDiff(minute, dbo.TimeOnly(first), @start) <
 DateDiff(minute, dbo.TimeOnly(first), dbo.TimeOnly(last));

Assuming that your dbo.TimeOnly function is essentially the same as Convert(time, {timefield}).

This will first find the difference between the new start time and the existing start times, then will find out if the duration of the new period is longer than that difference. This covers new periods that begin before or during existing ones.

The last clause compares the difference between the new start time and the existing start time to the duration of the existing period, to check whether the existing period is longer than the difference between them. Otherwise, the new start time will naturally be after the existing end time, which means it does not overlap.