Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations bkrike on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Group the overlap bookings???? 1

Status
Not open for further replies.
mrpro

mrpro

Programmer
Oct 11, 2004
64
GB
this query gets me all the overlap booking dates

select BookingId,convert(varchar(10),b1.Start,120) Stdate,convert(varchar(5),b1.Start,108) StTime,convert(varchar(5),b1.Finish,108) edtime
from Bookings b1
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


here is the data it is returning

BookingId stdate sttime edtime
1 2004-10-08 8.00 17.30
2 2004-10-08 9.00 14.30
3 2004-10-08 10.00 16.00
4 2004-10-08 11.00 12.30
5 2004-10-08 18.00 18.30
6 2004-10-08 18.00 18.30

what i wanted is some how i should be able to group these
overlapped bookings

Id's 1,2,3 and 4 are belongs to one group as they all overlap together and Id's 5 and 6 belong to another group
like wise we need to identify..

this is basically to have different background for each group at my front end
any suggesions..

Thanks



 
Sort by date Sort by votes
This may do something like you want:

Code: 
CREATE TABLE #groups ( [ID] int IDENTITY( 1, 1 ),
					  col1 datetime,
					  col2 datetime
					)
INSERT INTO #groups ( col1, col2 )
	SELECT col1, col2
	FROM Bookings a
		INNER JOIN 
		(
			SELECT  a.BookingId,
					ISNULL( MIN( b.Start ), CAST( '' AS datetime ) ) AS col1,
					ISNULL( MAX( b.Finish ), CAST( '' AS datetime ) ) AS col2
			 FROM Bookings a
				LEFT JOIN Bookings b ON b.Finish < a.Start
			 GROUP BY a.BookingId
		) AS b ON b.BookingId = a.BookingId
	GROUP BY col1, col2
	ORDER BY col1, col2

SELECT CONVERT(VARCHAR(10),a.Start,120) AS Stdate,
	   CONVERT(VARCHAR(5),a.Start,108) AS StTime,
	   CONVERT(VARCHAR(5),a.Finish,108) AS edtime,
	   #Groups.[ID] AS Group_ID
	FROM Bookings a
	INNER JOIN (
				SELECT  a.BookingId,
						ISNULL( MIN( b.Start ), CAST( '' AS datetime ) ) AS col1,
						ISNULL( MAX( b.Finish ), CAST( '' AS datetime ) ) AS col2
				 FROM Bookings a
					LEFT JOIN Bookings b ON b.Finish < a.Start
				 GROUP BY a.BookingId
			  ) AS b ON b.BookingId = a.BookingId
	INNER JOIN #Groups ON #Groups.col1 = b.col1 AND #Groups.col2 = b.col2

Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.
 
Upvote 0 Downvote
Zhavic Thank you for sending me this query

i have tested this againest my data and it looks great and it is what i am expecting also.But i am trying to refine your query to suit my output but no success for what so ever..

i just could not follow you in the grouping of your second select statment.

here is the query i have written which actually gives me any conflicts in my bookings

select b1.BookingId,convert(varchar(10),
b1.Start,120)StartDate,convert(varchar(10),
b1.Finish,120) EndDate,
convert(varchar(5),b1.Start,108) StartTime,
convert(varchar(5),b1.Finish,108) EndTime,
c1.Name Customer,r1.Name ResName

from (((Bookings b1 left join customers c1

on b1.customerId=c1.CustomerId)left join viewItems vi
on b1.ResourceId=vi.ResourceId)inner join resources r1
on b1.ResourceId=r1.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

thank you for any help..




 
Upvote 0 Downvote
Not sure, but try this:

Code: 
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 b.Finish < b1.Start
				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
		/* your EXISTS statement */
    GROUP BY col1, col2
    ORDER BY col1, col2
	
SELECT a.BookingId,
	   CONVERT( varchar(10),a.Start,120) AS Stdate,
       CONVERT( varchar(10), a.Finish, 120 ) AS EndDate,    
       CONVERT( varchar(5),a.Start,108) AS StTime,
       CONVERT( varchar(5),a.Finish,108) AS edtime,
       #Groups.[ID] AS Group_ID,
		c1.Name AS Customer,
		r1.Name AS ResName
    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 b.Finish < b1.Start
					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
    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

Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.
 
Upvote 0 Downvote
Zhavic this is perfect thanks for the help you definetly deserve a star for this..

i have just added
order by #Groups.ID

 
Upvote 0 Downvote
zhavic,

there are couple of problems i found which i couldn't solve

sttime edtime GroupId
09.00 17.30 3
10:00 12:30 3
17.30 18:00 4

according to the data these three should be grouped together but our query is grouping the first two and giving them the same Groupid and leaving the last one with different GroupId

and also i have added distinct keyword and order by statements to your second select statment to get the result correct but if i use

order by #Groups.ID, convert(varchar(10),a.Start,120)
it is giving me the error saying

ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

any thoughts why it is not grouping the above three together..





 
Upvote 0 Downvote
This is because first and third row has the same time 17.30

Try to change this row :

LEFT JOIN Bookings b ON b.Finish < b1.Start

to this one:

LEFT JOIN Bookings b ON b.Finish <= b1.Start

in the queries.

( I can't test it now )

Zhavic



---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.
 
Upvote 0 Downvote
no success zhavic
here is my entire sp which you can have a look at it..


<code>
drop table #groups
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 b.Finish <= b1.Start
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 >='20041011' and b1.Finish <='20041018' and vi.viewId=711
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 <b>distinct</b> 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 b.Finish <= b1.Start
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 >='20041011' and b1.Finish <='20041018' and vi.viewId=711
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
order by Group_ID,Startdate,StTime

</code>
 
Upvote 0 Downvote
sorry to bother you

when i changed the last booking starttime to 12:30 or less then it is participating in the same group any time beyond this will go for seperate groupid

i think this statement will be causing the problem
Code: 
left join Bookings b on b.Finish <= b1.Start
any help

thanks
 
Upvote 0 Downvote
i figured this out this problem
here is what i have changed in my sp

Code: 
b1.Start <=b.Finish

but still getting the error when i use dynamic order by clause in my sp

it is giving this error

ORDER BY items must appear in the select list if SELECT DISTINCT is specified

i am using this statement
Code: 
order by Group_ID,case 
when @SortOrder=1 then convert(varchar(10),a.Start,120) 
when @SortOrder=6 then convert(varchar(5),a.Start,108)
when @SortOrder=7 then convert(varchar(5),a.Finish,108)
when @SortOrder=2 then r1.Name
when @SortOrder=3 then bt1.Name
when @SortOrder=4 then c1.Name
when @SortOrder=5 then Jobs.JobName
when @SortOrder=8 then U1.Name
else convert(varchar(10),a.Start,120) 
end

can any one shed some light on to this

thanks
 
Upvote 0 Downvote
I was testing your query but I think there are still some problems with some groups.
I will look at this later ( now I am in the office )


To your second problem:
This is because you are using SELETC DISTINCT clause in select statement.

You need to add this one column to the select list:

Code: 
SELECT DISTINCT ...,
#groups.[ID] AS Group_ID,
[COLOR=blue]case
when @SortOrder=1 then convert(varchar(10),a.Start,120)
when @SortOrder=6 then convert(varchar(5),a.Start,108)
when @SortOrder=7 then convert(varchar(5),a.Finish,108)
when @SortOrder=2 then r1.Name
when @SortOrder=3 then bt1.Name
when @SortOrder=4 then c1.Name
when @SortOrder=5 then Jobs.JobName
when @SortOrder=8 then U1.Name
else convert(varchar(10),a.Start,120)
end AS Some_column_name[/color]

or you need to use GROUP BY clause with all columns from select list without DISTINCT clause in the select statemen:

Code: 
[COLOR=blue]group by a.BookingId,
        a.Start,
        a.Finish,    
        #groups.[ID][/color]
order by Group_ID,case
when @SortOrder=1 then convert(varchar(10),a.Start,120)
when @SortOrder=6 then convert(varchar(5),a.Start,108)
when @SortOrder=7 then convert(varchar(5),a.Finish,108)
when @SortOrder=2 then r1.Name
when @SortOrder=3 then bt1.Name
when @SortOrder=4 then c1.Name
when @SortOrder=5 then Jobs.JobName
when @SortOrder=8 then U1.Name
else convert(varchar(10),a.Start,120)
end

Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.
 
Upvote 0 Downvote
thanks zhavic for taking time to have a look at my problem
solution you gave me for dynamic order by is working great.

here i am giving more details about the first problem which i hope can help you in identifying the problem in the query

if i use
Code: 
left join Bookings b on b1.Start <=b.Finish  then

i am missing the Grouping of 11/10/2004 and getting the result as 

id  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
Code: 
left join Bookings b on b1.Start b1.Finish >=b.start then

i am missing the Grouping of 08/10/2004 and getting the result as

id  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

thanks
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Cpreston
  • Locked
  • Question
Group results into one row 3
2
Replies
37
Views
2K
Chris Miller
Chris Miller
Steve Yu
  • Locked
  • Question
How secure is MS SQL on the Internet vs behind firewall inside a LAN 1
Replies
3
Views
589
Chris Miller
Chris Miller
grnzbra
  • Locked
  • Question
SELECT TOP 1 of Each of a Group
Replies
5
Views
581
grnzbra
grnzbra
peac3
  • Locked
  • Question
fill the gap of hierarchy data
Replies
11
Views
1K
Chris Miller
Chris Miller
leifoet
  • Locked
  • Question
How can I update the input of a form (rows and columns) to the database in one query ?
Replies
1
Views
400
Chris Miller
Chris Miller

Part and Inventory Search

Sponsor

Back
Top