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.

Microsoft SQL Server 2005

Last Comment

clintnash

8/22/2022 - Mon

dsacker

9/9/2010

Try this:

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

Open in new window

Guy Hengel [angelIII / a3]

9/9/2010

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

9/9/2010

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

GET A PERSONALIZED SOLUTION

Ask your own question & get feedback from real experts

Find out why thousands trust the EE community with their toughest problems.

clintnash

9/9/2010

ASKER

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

Your help has saved me hundreds of hours of internet surfing.

fblack61

Guy Hengel [angelIII / a3]

9/9/2010

did you try mine? did it not work?

clintnash

9/9/2010

ASKER

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