what is wrong in my query..

Hi sql guru's
i am having trouble finding problem with my query can any one in the forum have a look at this and find out what's wrong with this query
i will explain problem with as much information as i can

actual data

BookingId start finish

1 13/10/2004 09:00:00 13/10/2004 17:30:00
2 13/10/2004 10:00:00 13/10/2004 12:30:00
3 13/10/2004 17:30:00 13/10/2004 18:00:00
4 08/10/2004 09:00:00 08/10/2004 18:00:00
5 08/10/2004 13:30:00 08/10/2004 18:00:00


current result

if i use b1.Start <=b.Finish then
i am missing the Grouping of 11/10/2004 and getting the result as

Bookignid start end sttime edtime GroupId
1 2004-10-11 2004-10-11 09:00 17:30 1
2 2004-10-11 2004-10-11 10:00 12:30 2
3 2004-10-11 2004-10-11 17:30 18:00 2
4 2004-10-08 2004-10-08 09:00 18:00 3
5 2004-10-08 2004-10-08 13:30 18:00 3

if i use b1.Finish >=b.start then
i am missing the Grouping of 08/10/2004 and getting the result as

Bookignid start end sttime edtime GroupId
1 2004-10-11 2004-10-11 09:00 17:30 1
2 2004-10-11 2004-10-11 10:00 12:30 1
3 2004-10-11 2004-10-11 17:30 18:00 1
4 2004-10-08 2004-10-08 09:00 18:00 2
5 2004-10-08 2004-10-08 13:30 18:00 3


expected result

Bookignid start end sttime edtime GroupId
1 2004-10-11 2004-10-11 09:00 17:30 1
2 2004-10-11 2004-10-11 10:00 12:30 1
3 2004-10-11 2004-10-11 17:30 18:00 1
4 2004-10-08 2004-10-08 09:00 18:00 2
5 2004-10-08 2004-10-08 13:30 18:00 2


my Query looks bit untidy but once you copy in to Notepad it looks ok..

i have two select statements

first select will dump data in to temp table #groups.

second select statement will join the temp table

i think problem with

LEFT JOIN Bookings b ON b.Finish < b1.Start in my quey but no idea what's wrong there.


create table #groups ( [ID] int IDENTITY( 1, 1 ),
col1 datetime,
col2 datetime
)
insert into #groups ( col1, col2 )
select b.col1, b.col2
from Bookings a
inner join
(
select b1.BookingId,
ISNULL( MIN( b.Start ), CAST( '' AS datetime ) )AS col1,
ISNULL( MAX( b.Finish ), CAST( '' AS datetime ) ) AS col2
from Bookings b1
inner join resources r1 on b1.ResourceId=r1.ResourceId
left join Bookings b on b1.Start <=b.Finish
left join customers c1 on b1.customerId=c1.CustomerId
left join viewItems vi on b1.ResourceId=vi.ResourceId
where exists( select BookingId, start, finish, resourceID
from Bookings b2
where b1.Bookingid <> b2.Bookingid and
b1.start <= b2.finish and
b1.finish >= b2.start and
b2.ResourceID = b1.ResourceID)
and b1.start >=@Startdate and b1.Finish <= @Enddate and vi.viewId=@viewId
group by b1.BookingId
) as b on b.BookingId = a.BookingId
where exists (
select BookingId from Bookings b2
where a.Bookingid <> b2.Bookingid and a.start <= b2.finish and a.finish >= b2.start AND b2.ResourceID = a.ResourceID
)
group by col1, col2
order by col1, col2

select a.BookingId,
convert( varchar(10),a.Start,120) AS Startdate,
convert( varchar(10), a.Finish, 120 ) AS EndDate,
convert( varchar(5),a.Start,108) AS sttime,
convert( varchar(5),a.Finish,108) AS edtime,
c1.Name AS Customer,
r1.Name AS ResName,
bt1.Name as BookingType,U1.Name Madeby,Jobs.JobName JobName,
#groups.[ID] AS Group_ID
from Bookings a
inner join (
select b1.BookingId,
ISNULL( MIN( b.Start ), CAST( '' AS datetime ) ) AS col1,
ISNULL( MAX( b.Finish ), CAST( '' AS datetime ) ) AS col2
from Bookings b1
inner join resources r1 on b1.ResourceId=r1.ResourceId
left join Bookings b on b1.Start<=b.Finish left join viewItems vi on b1.ResourceId=vi.ResourceId
where exists( select BookingId
from Bookings b2
where b1.Bookingid <> b2.Bookingid and
b1.start <= b2.finish and
b1.finish >= b2.start and
b2.ResourceID = b1.ResourceID )
and b1.start >=@Startdate and b1.Finish <= @Enddate and vi.viewId=@viewId
group by b1.BookingId
) as b on b.BookingId = a.BookingId
inner join #Groups on #Groups.col1 = b.col1 AND #Groups.col2 = b.col2
inner join resources r1 on a.ResourceId=r1.ResourceId
left join customers c1 on a.customerId=c1.CustomerId
left join viewItems vi on a.ResourceId=vi.ResourceId
inner join BookingTypes bt1 on a.BookingTypeId=bt1.BookingTypeId
inner join Users u1 on a.MadeBy=u1.UserId
left join JobBookings jb on a.BookingId=jb.BookingId
left join centraltime.dbo.Jobs as Jobs on Jobs.JobCode = jb.JobCode
group by a.BookingId,
a.Start,
a.Finish,
#groups.[ID],
c1.Name,r1.Name,bt1.Name,U1.Name,Jobs.JobName



thanks for any help