Web Development

Ask something
Question Locked

How to check range against range

0 helpful
5 replies

by sarasami

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Hello, I have table Reserve that contains 2 columns Chick-in and Chick-out
I'm looking for query that check that startDate and EndDate are not enterleaved with the previous Chick-in and Chick-out. Please please Any help will be appreciated.

5 replies

by tony hopkinson

remove your email address, you are going to be spammed to heck.

You've got peer mail disabled and then you stick your email out in public.

As for your question

What do you mean by previous, the nearest date range in order of date ?

if you you've already made sure startdate <= enddate in data entry

to be invalid new enddate or new startdate (or both) must be between and existing range.

so if
select * from mydates where (newstartdate between indate and outdate) Or
(newenddate between indate and outdate)

returns no data you are good to go.

Hths

PS, you need to get out of the previous and next thinking.

Given

21/1/2008 - 23/1/2008
27/1/2008 - 30/1/2008

Select * From MyDates Order By StartDate Desc

which is previous and next ???

Previous and next are implicitly based on what order you select in.

by sarasami

First of all thanks Tony for the advice, and also for your reply
I mint by previous, the previous words which is Check-in and Check-out, I didn't mean by it previous the date.
I'm gona give an example to make my question clear
If I've a room in a hotel which is reserved between 1/1/2009(as check-in) and 3/1/2009(as check-out), and another customer want to make a reservation for the same room between 4/1/2009(as StartDate) and 6/1/2009(as EndDate), using a query how can I check that this room is available for the new customer , in another way how can check that the StartDate and EndDate are not in or interleaved with Check-in and Check-out date.

by ic-it

just to keep people of your back you may want to change it from - chick-in & chick-out to - check-in & check-out. Although it would be a great caption for a dude with a heavy dating schedule. ;-)

by tony hopkinson

Availability, always a head scratcher.

Reverse the logic, instead of looking to see if the room is free, look to see if it's already booked.

Select * from Bookings Where RoomNumber= 10
and (CheckInDate between '2009-01-04' and '2009-01-06') Or (CheckOutDate between '2009-01-04' and '2009-01-06')

That would say there are no bookings on teh 2/1/2009 though, so you need

Select * from Bookings Where RoomNumber= 10
and (CheckInDate between '2009-01-04' and '2009-01-06') Or (CheckOutDate between '2009-01-04' and '2009-01-06')
Or ('2009-01-04' between CheckInDate And CheckOutDate) Or ('2009-01-06' between CheckInDate And CheckOutDate)

You might find some mileage in a calendar table,
Room Date, AvailableYorN

Then you would have
10 1/1/2009 N
10 2/1/2009 N
10 3/1/2009 N
10 4/1/2009 Y
10 5/1/2009 Y
10 6/1/2009 Y

Then you check query becomes
Select * From Available Where Room = 10 and RoomDate Between SomeCheckIn and SomeCheckOut and availablyYN = N

Building the table is a bit of work in the booking and cancellation operations, but you can do things like add another column, and take the room out for refurbishment, have no rooms on holidays e.g. Xmas.

Stick a booking number on it, so you can say who has/had it booked. Look for all available rooms in a period....

This discussion is closed to new replies.