Grouping by contiguous Date ranges and multiple columns

Question

Post reply

Grouping by contiguous Date ranges and multiple columns

vj6396

Old Hand

Points: 333
More actions
February 18, 2012 at 4:56 am

#391591

Hi
Given the following table :
create table Booking
(BookingId int, RoomId int, RoomBookingStatusId byte, BookingDate datetime)
I am looking for a way to group the rows by RoomId , BookingStatusID for each continuous date range.
The following query gives me the FromDate and ToDate for each room + bookingstatus , without splitting by discontinuous date ranges.
(select RoomId,Count(RoomId) as [Days], Min([BookingDate ]) as FromDate,Max([BookingDate ]) as ToDate, RoomBookingStatusId,
from Room_Booking
group by RoomId,RoomBookingStatusId)
Any help appreciated..

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

This might give you an idea of how to do what you want to do.

http://www.sqlservercentral.com/articles/T-SQL/71550/

It is an excellent discussion by Jeff Moden with sample code to do what I think you want to do.

Edited spelling

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

vj6396 Old Hand Points: 333 More actions · Answer 2

Thanks..

http://www.sqlservercentral.com/articles/T-SQL/71550 had exactly what I needed. I hadn't quite understood the "multiple objects" when I saw the post earlier..

Regards

Ron McCullough SSC Guru Points: 63877 More actions · Answer 3

vj6396 (2/18/2012)
Thanks..
http://www.sqlservercentral.com/articles/T-SQL/71550 had exactly what I needed. I hadn't quite understood the "multiple objects" when I saw the post earlier..
Regards

There may be others with the same thought. Now if it would not be revealing of confidential data, may I ask you to help others by posting the code you write in answer to your problem, so that others may learn.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

vj6396 Old Hand Points: 333 More actions · Answer 4

Here's what I used

;with

groupeddates as

(

select uniquedate = [Date],

DateGroup = DATEADD(dd, - ROW_NUMBER() OVER (Partition by RoomId,RoomReservationStatusId ORDER BY RoomId, RoomReservationStatusId, ExtraBedCount, [Date]), [Date]),

RoomReservationStatusId, RoomId, ExtraBedCount

from Room_Booking

where ReservationId = @reservationId

group by RoomId, RoomReservationStatusId, ExtraBedCount, [Date]

)

integritec SSC Enthusiast Points: 114 More actions · Answer 5

@vj6396, thanks for sharing the code you used.

@celko, there's no need to be rude and dismissive about someone else's design. Some of your comments are universally valid, others are entirely personal preference. You cannot second-guess the circumstances that lead to a particular design: in my case and possibly in that of the OP, I have inherited someone else's DB design and must work within those constraints and therefore need to produce date ranges from individual date rows.

Other than the purpose of helping the OP, the next most important point of this thread is to give the rest of us a starting point for building similar solutions - particularly as this is high on the Google listings and is cross-linked elsewhere. To that effect, @vj6396's reply post adds a lot more value than yours.