WilliamStacey

Trying to figure this out with no luck yet.

Table (datetime columns assume all same day):

Seq InTime OutTime

1 12:00pm 3:00pm

2 1:00pm 4:00pm

3 11:am 1:00pm

4 5:00pm 6:00pm

I need some kind of self-join or correlated sub-query to do this that should return records 1,2,3 as they overlap each other.

TIA



Re: LINQ Project General Query overlapping date ranges query

Keith Farmer

On my way out, so don't have time to prep a real db, but does this do it

Code Block

(

from t1 in table

from t2 in table

where t1.Seq != t2.Seq

&& (

t1.InTime >= t2.InTime && t1.InTime <= t2.OutTime

||

t1.OutTime >= t2.InTime && t1.OutTime <= t2.OutTime

)

select t1

).Distinct()






Re: LINQ Project General Query overlapping date ranges query

WilliamStacey

That's the one. Thanks Keith!




Re: LINQ Project General Query overlapping date ranges query

Keith Farmer

As my brother would say: My head's not just a hatrack, after all!






Re: LINQ Project General Query overlapping date ranges query

dragon_ballz96

Hi,

I need a SQL query to do the following booking check:

I need to check if a new booking, 2007-12-28 13:15 to 15:30 is available.

Booking No Book From Book To

BN0001 2007-12-28 11:30 2007-12-28 12:45

BN0002 2007-12-28 13:00 2007-12-28 13:30

BN0003 2007-12-28 13:45 2007-12-28 14:15

There is an overlapping slot for BN0002 and BN0003

Both Book From and To are on the same day.

Just wonder if u can help me on it. Let me know. Thks.

Ryan