formating output of overlapping bookings..can we do this.. 1

[mrpro]

Hi

this query returns the following data(all the conflicting bookings) vongrunt and jonwolds helped me
to write this query now i need to extend this query..

select B1.BookingId,B1.start,B1.Finish
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 >='20041011' and B1.Finish <= '20041018'
and B1.ResourceId in(select ResourceId from viewItems where viewId=711)
Group by B1.BookingId,B1.start,b1.Finish
order by B1.start

returns this data

BookingId	Start			Finish

1052450720	2004-10-11 08:00	2004-10-11 17:30
1052450718	2004-10-11 09:00	2004-10-11 14:30
1052450721	2004-10-11 10:00	2004-10-11 18:00
1052450723	2004-10-11 18:00	2004-10-11 18:30
1052450727	2004-10-11 18:00	2004-10-11 18:30
1052450722	2004-10-13 09:00	2004-10-13 17:30
1052450724	2004-10-13 10:00	2004-10-13 12:30
1052450725	2004-10-13 17:30	2004-10-13 18:00
1052450735	2004-10-16 09:00	2004-10-16 10:30
1052450736	2004-10-16 09:00	2004-10-16 10:30

can we be able to Group each conflicting date range together
that means i wanted to print output like this

BookingId	Start			Finish		 GroupId	

1052450720	2004-10-11 08:00	2004-10-11 17:30 1	
1052450718	2004-10-11 09:00	2004-10-11 14:30 1
1052450721	2004-10-11 10:00	2004-10-11 18:00 1
1052450723	2004-10-11 18:00	2004-10-11 18:30 2
1052450727	2004-10-11 18:00	2004-10-11 18:30 2
1052450722	2004-10-13 09:00	2004-10-13 17:30 3
1052450724	2004-10-13 10:00	2004-10-13 12:30 3
1052450725	2004-10-13 17:30	2004-10-13 18:00 3
1052450735	2004-10-16 09:00	2004-10-16 10:30 4
1052450736	2004-10-16 09:00	2004-10-16 10:30 4

thanks for any help

 

[ESquared]

ORDER BY
   Start,
   BookingId

-------------------------------------
It is better to have honor than a good reputation.
(Reputation is what other people think about you. Honor is what you know about yourself.)

 

[mrpro]

ESquared thanks for the reply..but i think you haven't noticed my required output.

i need to have each conflicting group range will have unique GroupID..

this is the required output

BookingId   Start            Finish           [b][COLOR=red]GroupId[/color][/b]    

1052450720  2004-10-11 08:00 2004-10-11 17:30 1    
1052450718  2004-10-11 09:00 2004-10-11 14:30 1
1052450721  2004-10-11 10:00 2004-10-11 18:00 1
1052450723  2004-10-11 18:00 2004-10-11 18:30 2
1052450727  2004-10-11 18:00 2004-10-11 18:30 2
1052450722  2004-10-13 09:00 2004-10-13 17:30 3
1052450724  2004-10-13 10:00 2004-10-13 12:30 3
1052450725  2004-10-13 17:30 2004-10-13 18:00 3
1052450735  2004-10-16 09:00 2004-10-16 10:30 4
1052450736  2004-10-16 09:00 2004-10-16 10:30 4

thanks for any help

 

[ESquared]

Oh... now I see. That's a tad tricky. Hmmm. Sorry about the hasty response, before.

The problem is that one can't just join to find bookings that overlap a booking... because there could be other bookings that overlap one of those overlaps but don't overlap the first one.

I haven't figured out a way to do it in a single query/update, but here's a way to do it with a temp table and multiple updates. (A table variable could work as well as or better than a temp table, too.)

I'm showing my work tables so other people can play with it if they like.

CREATE TABLE
   #BookingGroups (
      BookingId int, -- ?
      Start smalldatetime,
      Finish smalldatetime,
      GroupNum int
   )
 
-- INSERT INTO #BookingGroups
--    (BookingId, Start, Finish)
--    SELECT
--       BookingId, Start, Finish
--    FROM YourAboveQuery
 
INSERT INTO #BookingGroups (BookingID, Start, Finish)
SELECT 1052450720, '2004-10-11 08:00', '2004-10-11 17:30' UNION
SELECT 1052450718, '2004-10-11 09:00', '2004-10-11 14:30' UNION
SELECT 1052450721, '2004-10-11 10:00', '2004-10-11 18:00' UNION
SELECT 1052450723, '2004-10-11 18:00', '2004-10-11 18:30' UNION
SELECT 1052450727, '2004-10-11 18:00', '2004-10-11 18:30' UNION
SELECT 1052450722, '2004-10-13 09:00', '2004-10-13 17:30' UNION
SELECT 1052450724, '2004-10-13 10:00', '2004-10-13 12:30' UNION
SELECT 1052450735, '2004-10-16 09:00', '2004-10-16 10:30' UNION
SELECT 1052450736, '2004-10-16 09:00', '2004-10-16 10:30'

--Assign unique numbers per booking, in order by date started.

UPDATE A
   SET GroupNum = Num
   FROM
      #BookingGroups A
      INNER JOIN (
         SELECT
            G1.BookingID,
            Num = Count(*)
         FROM #BookingGroups G1
            INNER JOIN #BookingGroups G2 ON G1.Start >= G2.Start
         GROUP BY
            G1.BookingID
      ) B ON A.BookingID = B.BookingID

-- For each overlap, set the one with the later time to the number of the earlier one. Do this until there are no more overlaps left that don't have the same number.

WHILE @@RowCount > 0
   UPDATE B
      SET B.GroupNum = A.GroupNum
   FROM
      #BookingGroups A
      INNER JOIN #BookingGroups B ON B.Start < A.Finish AND A.GroupNum < B.GroupNum

-- Reassign the numbers so they are sequential.

UPDATE A
      SET A.GroupNum = NewGroupNum
   FROM
      #BookingGroups A
      INNER JOIN (
         SELECT G1.GroupNum,
            NewGroupNum = Count(*)
         FROM (
               SELECT DISTINCT GroupNum FROM #BookingGroups
            ) G1 INNER JOIN (
               SELECT DISTINCT GroupNum FROM #BookingGroups
            ) G2 ON G1.GroupNum >= G2.GroupNum
         GROUP BY
            G1.GroupNum
      ) B ON A.GroupNum = B.GroupNum

SELECT * From #BookingGroups ORDER BY GroupNum, Start, Finish
   
DROP TABLE #BookingGroups

E²

P.S. For what it's worth, there's something wrong with your query. BookingID 1052450725 doesn't overlap with anything. It starts at the same time as 1052450722 finishes, which seems okay to me. So, your original query might be changed from >= to

[tt]B1.finish > B2.start[/tt]

Also please note that the time criteria you showed could miss some overlaps (unless business rules don't allow bookings that extend through midnight). The only way I see to fix this is, after the first insert into the temp table, do additional inserts to add the remaining overlapping bookings, until @@rowcount = 0.





-------------------------------------
It is better to have honor than a good reputation.
(Reputation is what other people think about you. Honor is what you know about yourself.)

 

[mrpro]

Thanks ESquared that's brilliant.
it is what i am expecting for..i am testing this with my data..it seems perfect..

i have understood you script half way through though

i didn't get you in while loop inner join

INNER JOIN #BookingGroups B ON B.Start < A.Finish AND A.GroupNum < B.GroupNum

if you have a minute can you explain what actully doing this bit.I think this is doing the trick.

Thanks

 

[ESquared]

Well, it's similar to the WHERE clause in the initial query you gave.

Pseudocode for "overlapping bookings" is "one booking starts before another ends."

So I express this with

B.Start < A.Finish

But that's not enough because B could have also finished before A started. So initially it required an additional clause

AND B.Finish > A.Start

But I had one additional requirement. I didn't want every booking to appear on the 'left' and 'right' sides of the join. I wanted the 'left' side of the join to have a lower booking than the 'right' side. So I needed

AND A.Start < B.Start

But for whatever reason, I happened to choose to use the sequence numbers, which you may see are assigned in order by start time. So it's effectively the same condition, but when I was writing the query, made more sense to me, as I had mentally switched from "working with times" to "working with groups."

A.GroupNum < B.GroupNum

Also note that the @@Rowcount thing is relying on the update affecting some rows. If you modify the query make sure you keep this in mind, because @@rowcount must be nonzero in order for the second update to run even once.

-------------------------------------
It is better to have honor than a good reputation.
(Reputation is what other people think about you. Honor is what you know about yourself.)