overlap bookings 1

[mrpro]

Hi
i am looking for an sql statement which returns the overlapping date ranges

here is the data in my table

bookingid start finish
1001 2004/10/11 08:00 2004/10/11 17:30
1002 2004/10/11 09:00 2004/10/11 14:30
1003 2004/10/11 10:00 2004/10/11 18:00
1004 2004/10/11 11:00 2004/10/11 12:30
1005 2004/10/11 18:00 2004/10/11 19:30

i only wanted to return
1001
1002
1003
1004

thanks for any help

mrpro.

 

[jonwolds]

This is the best I can do at the minute. Hopefully it can be built upon if required.

if object_id('tempdb..#Test') is not null
drop table #Test

set dateformat ymd

create table #Test
(
ident int IDENTITY(1,1)
,bookingid int
,start datetime
,finish datetime
)
insert into #Test

select
bookingid
,start
,finish
from
Test

select
cast(T.bookingid as varchar(4)) + '-' + cast(T1.bookingid as varchar(4)) Overlap
from
#Test T
left join
#Test T1 on T.ident = (T1.ident - 1)
where
cast(T.finish as smalldatetime) > cast(T1.start as smalldatetime)

 

[jonwolds]

if object_id('tempdb..#Test') is not null
drop table #Test

set dateformat ymd

create table #Test
(
ident int IDENTITY(1,1)
,bookingid int
,start datetime
,finish datetime
)
insert into #Test

select
bookingid
,start
,finish
from
Test

select
bookingid
from
(
select
T.bookingid Tbookingid
,T1.bookingid T1bookingid
from
#Test T
left join
#Test T1 on T.ident = (T1.ident - 1)
where
cast(T.finish as smalldatetime) > cast(T1.start as smalldatetime)

) a
join
#Test b on (a.Tbookingid = b.bookingid or a.T1bookingid = b.bookingid)
group by
bookingid

 

[vongrunt]

Suppose there are two intervals: D1 and D2. To detect intersection use something like:

WHERE D1Start <= D2End and D1End >= D2Start

From your data it appears that end of interval is inclusive (18:00, not 17:59:59) so:

select A.* 
from blah A
where exists (select * from blah B where A.bookingid<>B.bookingid  and A.start < B.finish and A.finish >= B.start)

 

[jonwolds]

hmmmmm..

vongrunt that seems somewhat simpler.

I shall have to remember that one.

 

[mrpro]

Thank you very much to vongrunt and jonwolds's to quickly solved my problem..

yaa the solution vongrunt has given is very simple and easy one..so it solved my problem..

 

[mrpro]

Hi vongrunt
i am sorry i couldn't figure this out yesterday as this still returns a booking if there are no overlap bookings on a perticular day..some thing weird happening..

can you please help me out ..

Thanks


select A.*
from Bookings A
where exists (
select BookingId from Bookings B
where A.Bookingid <> B.Bookingid and A.start < B.finish and A.finish >= B.start
)
and A.ResourceId=289 and A.start >='20041007' and A.Finish <= '20041008'
order by A.start


Thanks

 

[vongrunt]

First check is there any record for that period and ResourceID:

select A.* 
from Bookings A
where A.ResourceId=289 and A.start >='20041007' and A.Finish <= '20041008'

Also: is there a possibility that columns start/finish contain NULLs ?

 

[mrpro]

vongrunt, i did run this query in my Q.analyzer and it returned one record and also start/finish are never be empty.

Thanks

 

[vongrunt]

If it returns one record there is nothing to overlap, right?

 

[mrpro]

yes it only needs to return overlapped bookings..

 

[vongrunt]

Hm... probably you have overlapped interval but from another resource. Add AND A.ResourceID=289 into EXISTS() and see is that true.

Or... there is another booking that completely overlaps specified interval (for example, from Oct 5th to Oct 15th).

 

[vongrunt]

Damn... this won't do anything. Use AND B.ResourceID = A.ResourceID or something similar.

 

[mrpro]

i did tried with this but no success..interestingly solution given by jonwolds works fine but did not undestand the code bit well..your query looks simple so..
any

select A.*
from Bookings A
where exists (
select BookingId from Bookings B
where A.Bookingid <> B.Bookingid and A.start < B.finish and A.finish >= B.start AND A.ResourceID=289
)
and A.ResourceId=289 and A.start >='20041004' and A.Finish < '20041005'
order by A.start

 

[mrpro]

ur great..its working..once again rescued me..thanks