Hi Rob,

just to let you know. I adjusted your SP for my needs and found out following:

Ok, I didn't mentioned that I can have also day 0 (zero); because of that I had to bring in a isnull function. (See first statement for first where clausel)

select h_min.hotel_id, h_min.theday, h_max.theday - h_min.theday + 1

from hotels h_min

join

hotels h_max

on h_min.hotel_id = h_max.hotel_id

where h_max.theday =

isnull((select max(h1.theday)

from hotels h1

where h1.hotel_id = h_min.hotel_id

and h1.theday >= h_min.theday

and not exists (select * from hotels h2

where h2.theday between h_min.theday and h1.theday

and h2.hotel_id <> h1.hotel_id)

), 0)

and not exists (select * from hotels h_last where h_last.theday = h_min.theday - 1 and h_last.hotel_id = h_min.hotel_id)

With this change it works now also if "theday" = 0.

Thanks again,

Tassilo