asked on

Comparing date times start time and end time using between

I cant seem to get the right combination to generate the proper results. I need to compare the start date and end date to determine if rows are overlaping. Example

RowID StartTime EndTime
1 9/11/2010 08:00:00 9/11/2010 09:00:00
2 9/11/2010 07:30:00 9/11/2010 08:30:00
3 9/11/2010 08:30:00 9/11/2010 09:30:00
4 9/11/2010 09:30:00 9/11/2010 10:30:00

I need to return row 1,2 and 3 but not row 4 since the start time is the same as the end time of the previous appointment. Any help is greatly appreciated.

dsacker

Try this:

select RowID,
       StartTime,
       EndTime
from   MyTable
where  StartTime NOT IN (select EndTime from MyTable)

Open in new window

Guy Hengel [angelIII / a3]

what about this:

select t.*
 from yourtable t
 where exists( select null from yourtable o where o.starttime < t.endtime and o.endtime > t.starttime )

Open in new window

ASKER CERTIFIED SOLUTION

Mehdi Javan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

clintnash

ASKER

Short of a missing ) at the end, this query returns the correct results. Thanks to each of your for your help this morning.

Guy Hengel [angelIII / a3]

did you try mine? did it not work?

clintnash

ASKER

Angel, Actually I tried them all, the first returned two of the three rows, yours returned all four rows. Thank you...