sql - Check whether the schedule overlaps between days -

i need find whether new schedule overlaps existing schedules.

this "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 using scalar function dbo.timeonly extracting time part datetime fields.

my selection criteria 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  * intervals       ( not ( dbo.timeonly(last) < @start or  @end < dbo.timeonly(first) ) ) 

this return 1st , 2nd records. got logic check whether 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 write query return 3rd record criteria in second case?

update

i give more details problem

different people managing particular event time duration in day.

for monday, schedule format this

    id  start       end          user   days          1  00:01    08:00     'a'    1      2  08:01    04:00 pm     'b'    1      3  04:00 pm    00:00     'c'    1 

for tuesday's

     4  08:01    04:00 pm     'b'    2      5  07:00 pm    07:00     'c'    2 

for wednesday's

     6  08:01    04:00 pm     'a'    4      7  10:00 pm    08:00     'b'    4 

here days stored in bit value format ie

monday=1,tuesday=2,wednesday=4,thursday=8,friday=16,saturday=32 , sunday=64

when creating schedule particular day, should not overlap between times. sql query checking schedules exists while creating new schedule particular day.

for particular event time (say occured @ 04:00 on tuesday) find correct schedule (will "5") falls between start , end time.

change select this:

select  * intervals  ( ( dbo.timeonly(last) > dbo.timeonly(first) , not (dbo.timeonly(last) < @start or  @end < dbo.timeonly(first)) )  or  ( dbo.timeonly(last) < dbo.timeonly(first) , ( @start >= dbo.timeonly(first) or @end <= dbo.timeonly(last) or (@start < dbo.timeonly(first) , @end > dbo.timeonly(last)) ) ) ) 

i might've missed parenthesis somewhere, hope not.

the concept here query 2 main groupings combined or. first clause checks intervals last > first , copy of existing query addition of last > first condition, while 2nd clause checks intervals last < first.

in case last < first, there 3 ways interval can overlap:

• start after interval's first
• end before interval's last
• start , end engulf interval, i.e., start before first , end after last

any 1 of these 3 conditions mean schedule check within existing interval, 3 conditions combined ors.