SQL challenge: coelesce consecutive and overlapping bookings for a room

I've got a little SQL challenge and the solution is eluding me.

I have a table that stores when a person has booked a room in a boarding
house. I need a SQL statement or statements that coalesce consecutive and
overlapping bookings for same person and room into one booking (one result
row).

Dates are inclusive. A consecutive booking occurs when a person is moving
out on 1/10/2000 and moving in on 1/11/2001. We could consider adding a
time to the date: time for move_in as 0:00 and move_out as 24:59:59.999.

Sample table, data, and desired resultset follows.

Thanks for any help.
Don.
__________________________

create table booking (id int, room int,
move_in datetime, move_out datetime,
primary key (id, room, move_in) )
insert into booking values (1, 11, '1/01/2001', '1/10/2001')
insert into booking values (1, 11, '1/10/2001', '1/15/2001')
insert into booking values (1, 12, '1/15/2001', '1/17/2001')
insert into booking values (2, 21, '1/01/2001', '1/05/2001')
insert into booking values (2, 21, '1/10/2001', '1/20/2001')
insert into booking values (3, 31, '1/01/2001', '1/20/2001')
insert into booking values (3, 31, '1/05/2001', '1/10/2001')
insert into booking values (3, 31, '1/17/2001', '1/25/2001')
insert into booking values (4, 41, '1/01/2001', '1/10/2001')
insert into booking values (4, 42, '1/05/2001', '1/06/2001')
insert into booking values (5, 51, '1/01/2001', '1/10/2001')
insert into booking values (5, 51, '1/11/2001', '1/20/2001')
/*
desired resultset:

id room move_in move_out
--- ----- ---------- ----------
1 11 1/01/2001 1/15/2001
1 12 1/15/2001 1/17/2001
2 21 1/01/2001 1/05/2001
2 21 1/10/2001 1/20/2001
3 31 1/01/2001 1/25/2001
4 41 1/01/2001 1/10/2001
4 42 1/05/2001 1/06/2001
5 51 1/01/2001 1/20/2001

*/

don try GROUP BY ID and Room
Also grab the MAX(out time) and the MIN(in time)

I think that should do it..
Sorry I did not teste it...

"Don Arsenault" <snows...@antelecom.net> wrote in message
news:uA0gSxp8AHA.2068@tkmsftngp03...

Nah. It's more complicated than that. Simple min(move_in)/max(move_out)
will not return separate rows for non-consecutive bookings. Thanks for the
attempt.

Don.

"Angel" <sqlguru...@hotmail.com> wrote in message
news:#7pkIlq8AHA.564@tkmsftngp02...

i am sorry i dont understand your problem then....
I guess i could help you, but mahybe someone here that understand what you
want will help....
I have never failed finding the best solution but after i know what is
requiered,
They way i saw it is just MIN MAX stuff..even if they overlap you grab the
head and tail......
but....

news:etuNtsq8AHA.1992@tkmsftngp02...

The following worked in Oracle (I do not have access to MSSQL). You will
have to substitute move_in - 1 and move_out + 1 with their DATEADD
equivalents

CREATE VIEW in_v
(id,room, move_in_date)
AS
SELECT id,room, move_in
FROM booking b1
WHERE NOT EXISTS (SELECT * FROM booking b2
WHERE b1.id = b2.id
AND b1.room = b2.room
AND b1.move_in, - 1 BETWEEN b2.move_in AND b2.move_out);

CREATE VIEW out_v
(id,room, move_out_date)
AS
SELECT id,room, move_out
FROM booking b1
WHERE NOT EXISTS (SELECT * FROM booking b2
WHERE b1.id = b2.id
AND b1.room = b2.room
AND b1.move_out, 1 BETWEEN b2.move_in AND b2.move_out);

SELECT mi.id, mi.room, mi.move_in_date,
( SELECT min(mo.move_out_date)
FROM out_v mo
WHERE mi.id=mo.id
AND mi.room=mo.room
AND mi.move_in_date < mo.move_out_date)
FROM in_v mi

Greetings,

This solution is based on the work of Dr,Wolf J. Flywheel who showed
how to solve problems like this by greating virtual groups within runs
of consecutive integers.Virtual groups are formed when the difference
between consecutive integers are greater than 1.Of course dates can
also be used.The solution is actual easy to implement is extremely
powerful.
The solution requires a table digits (numbers) starting at 0.ie.

select top 10 * from daydigits

digit
-----------
0
1
2

First step is preprocessing the data creating consecutive days for each
room.

create table #rooms(rd int identity,[id] int,room int,days datetime)
insert #rooms ([id],room,days)
select distinct [id],room,dateadd(day,digit,move_in) as days
from #booking,daydigits
where dateadd(day,digit,move_in)<=move_out
order by [id],room,days

This is the basic query that has to be filled in for the particular data.

select e.[id],e.room,e.days,
(select count(*)+1
from (select a.[id],a.room,a.days,a.days-isnull((select
max(b.days) from #rooms AS b
where b.room=a.room and b.days<a.days),(select min(c.days)
from #rooms AS c)) AS grp
from #rooms AS a) as d
where d.room=e.room and d.days<=e.days and d.grp>1) as vgrp into #v1
from
(select a.[id],a.room,a.days,a.days-isnull((select max(b.days) from
#rooms AS b
where b.room=a.room and b.days<a.days ),(select min(c.days) from #rooms
AS c)) AS grp
from #rooms AS a) as e
order by 1,2,3

If you work your way through the query you will see how the count function
forms virtual groups that increment consecutively within each room.
The actual numeric value of the virtual group is not important.

select * from #v1

By using the vgrp (virtual group) as a grouping variable you can
answer just about any question regarding data within groups.In this
case data within room and virtual group,

select [id],a.room,convert(varchar(10),[in],101) as [in],
convert(varchar(10),[out],101) as [out],cnt
from
(select room,vgrp,min(days) as [in],max(days) as [out],
count(*) as cnt
from #v1
group by room,vgrp) as a
join (select [id],room from #booking group by [id],room) as b on
b.room=a.room
order by 1,2,3

Viola.

id room in out cnt
----------- ----------- ---------- ---------- -----------
1 11 01/01/2001 01/15/2001 15
1 12 01/15/2001 01/17/2001 3
2 21 01/01/2001 01/05/2001 5
2 21 01/10/2001 01/20/2001 11
3 31 01/01/2001 01/25/2001 25
4 41 01/01/2001 01/10/2001 10
4 42 01/05/2001 01/06/2001 2
5 51 01/01/2001 01/20/2001 20

stevie
www.angelfire.com/ny4/rac/
Inquire for new s2k beta.

Amazing! :-) It's so complicated. I went through it to figure out how it
works. Even my summary seems complicated:

for each person and room...
** find bookings that don't start within
or the day after another booking.
i.e. the first booking in a group of
overlapping or consecutive bookings.
move_in for resultset is move_in from those bookings.
** for each of those bookings...
** find bookings that don't end within
or the day before another booking.
i.e. the last booking in a group of
overlapping or consecutive bookings.
include only bookings that end
after the resultset move_in.
move_out for the resultset is the
minimum move_out from those bookings.

But it makes sense!

Thanks a lot.

I just replaced the date arithmetic with dateadd(), converted the views to
derived tables, then converted that to correlated subqueries, and ended up
with a single SQL statement:

SELECT mi.id, mi.room, mi.move_in,
move_out =
(
SELECT min(mo.move_out)
FROM booking mo
WHERE NOT EXISTS
(

WHERE mo.id = b2.id
AND mo.room = b2.room
AND dateadd(day, 1, mo.move_out)
BETWEEN b2.move_in AND b2.move_out
)
AND mi.id=mo.id
AND mi.room=mo.room
AND mi.move_in < mo.move_out
)
FROM booking mi
WHERE NOT EXISTS
(

WHERE mi.id = b2.id
AND mi.room = b2.room
AND dateadd(day, -1, mi.move_in)
BETWEEN b2.move_in AND b2.move_out
)

Don.

"Isaac Blank" <izb...@yahoo.com> wrote in message
news:u3l$w7q8AHA.1560@tkmsftngp04...

Thanks, especially for the great explanation. I like Isaac Blank's solution
for its "relative" simplicity. However, I see your point about how this
technique can be used for so much more.

Don.

"Steve Dassin" <try...@aol.com> wrote in message
news:Oed2xPr8AHA.2124@tkmsftngp02...

Isaac,

The out_v view won't work as it stands because you're missing an operator.
You need to add 1 to b1.move_out. The last restriction needs to read:

AND b1.move_out + 1 BETWEEN b2.move_in AND b2.move_out

I don't recall that the commas in the restrictions is valid Oracle syntax,
either. But I may be mistaken on that.

BTW, SQL Server 7.0 and higher support the date addition syntax just like
Oracle does.

Linda

SELECT DISTINCT bk.id, bk.room,
CONVERT(CHAR(10),bk.move_in,101) as move_in,
CONVERT(CHAR(10),COALESCE((SELECT Max(move_out)
FROM booking b2
WHERE bk.id = b2.id
AND bk.room = b2.room
AND (bk.move_out = b2.move_in - 1
OR bk.move_out = b2.move_in
OR bk.move_out BETWEEN b2.move_in AND
b2.move_out)),bk.move_out),101) as move_out
FROM booking as bk INNER JOIN (SELECT b1.id,
b1.room,
(SELECT Min(move_in) AND (b1.move_in BETWEEN b2.move_in
AND b2.move_out
OR b2.move_out = b1.move_in - 1))
as Move_in
FROM booking as b1) as b3
ON bk.move_in = b3.move_in AND bk.id = b3.id AND bk.room = b3.room

RESULT:

id room move_in move_out

1 12 01/15/2001 01/17/2001
2 21 01/01/2001 01/05/2001
2 21 01/10/2001 01/20/2001
3 31 01/01/2001 01/25/2001
4 41 01/01/2001 01/10/2001
4 42 01/05/2001 01/06/2001
5 51 01/01/2001 01/20/2001

--
Darren Brinksneader MCDBA, MCSE+I, CNE, CCA, MCT, CTT

"lindawie" <lind...@my-deja.com> wrote in message
news:eJvpEwr8AHA.2020@tkmsftngp03...

You are right, it just was a cut-n-paste error

I wish I knew. I have only worked with 6.5 and 4.2

issac you amaze me every time man...i gotta handle to you
u r one hell of and sql programmer or you got all this solutions
written some where..
:O)

not my nest day..i could not even understand the problem,...

bye

news:eOnPl7r8AHA.1864@tkmsftngp07...

Or this...WHERE move_in IN (SELECT Min(move_in)
FROM booking b2 AND (bk.move_in BETWEEN b2.move_in AND b2.move_out
OR b2.move_out = bk.move_in - 1))

This is a lot cleaner than my original post. ;-)

"Darren Brinksneader" <dbrink...@atai.com> wrote in message
news:OvmCH5r8AHA.2016@tkmsftngp03...

Real cool. And thanks. Don.

news:#fAC2wz8AHA.1528@tkmsftngp05...

This will only coalesce two consecutive bookings, but not three or more

Isaac

news:#fAC2wz8AHA.1528@tkmsftngp05...

This should fix it...

AND bk.move_in < b2.move_out),bk.move_out),101)

--

news:eubt3J28AHA.2204@tkmsftngp02...

Now it spoiled the result for id 2:

1 11 1/1/01 1/10/01
1 12 1/15/01 1/17/01
2 21 1/1/01 1/20/01
2 21 1/10/01 1/20/01
3 31 1/1/01 1/25/01
4 41 1/1/01 1/10/01
4 42 1/5/01 1/6/01
5 51 1/1/01 1/20/01

The reason I keep testing your solution is simple - I can not understand how
it works, so I have to go and try breaking it - so far successfully :-(

news:#mDoRW28AHA.1260@tkmsftngp04...

Cool. You keep breaking and I'll keep trying. If I'm not anything else, at
least I am persistent. ;-)

news:ezePre28AHA.1588@tkmsftngp03...

Alright Isaac, here's another one...

SELECT id,
room,
CONVERT(CHAR(10),move_in,101) as move_in,
CONVERT(CHAR(10),(SELECT min(move_out)
FROM booking bk2
WHERE bk1.id = bk2.id
AND bk1.room = bk2.room
AND NOT EXISTS (SELECT *
FROM booking bk3
WHERE bk2.id = bk3.id
AND bk2.room = bk3.room
AND bk2.move_out + 1 BETWEEN bk3.move_in AND
bk3.move_out)),101) as move_out
FROM booking bk1

FROM booking bk2
WHERE bk1.id = bk2.id
AND bk1.room = bk2.room
AND bk1.move_in - 1 BETWEEN bk2.move_in AND
bk2.move_out)

news:O9VZXj28AHA.2264@tkmsftngp07...

Yes, but now how this is different from what Don came up with, based on my
original approach? Just a slight variation of the WHERE clause both in the
main query and in the subquery. :-)

news:u6fVZN48AHA.1516@tkmsftngp04...