Reservations can overlap date ranges in three ways:
Start date of new reservation falls on start to end date date range
End date falls on this same date range.
Start date of new reservation precedes reservation start date and End date of new reservation comes after end date existing reservation date.
Thus, you may need to craft a more sophisticated portal filter.
I can't really parse your example filter expression and have it make sense to me.
Are you looking for a list of available rooms in this portal?
Is the criteria that the room be a room with at least as many beds as needed for the new reservation and which is not reserved for the date range of the new reservation?
I'll try to be more descriptive.
Each room has a declared number of beds, meaning max number of people who can be assigned to that room at any given date.
On creating new reservation, I want to have a portal, that shows the rooms, that at have at least one free bed for the time the reservation is created.
So, for each room, it should count all the reservations for that room, where the existing reservation overlaps the one we create
new_reservation_start is between existing_reservation_start and existin_reservation_end
new_reservation_end is between existing_reservation_start and existin_reservation_end
count the matching results, and if the count is equal or greater than the number of beds the room should not be visible in the portal.
I'll try to post an example of data and the results I am hoping to get.
Table room types:
Test case 1:
Setting reservation for from 2011-11-26 to 2011-11-28
room id_room 1
room id_room 2
room id_room 3
Test case 2:
Setting reservation for from 2011-11-20 to 2011-11-28
room id_room 3
Test case 1:
Setting reservation for from 2011-11-23 to 2011-11-23
room id_room 1
room id_room 3
Its your description of this part:
I want to have a portal, that shows the rooms, that at have at least one free bed for the time the reservation is created.
I think that's just imprecise language but it reads like you are renting out individual beds instead of rooms. (Bed 1 is occupied but bed 2 in the same room is free so list this room....)
Unless this is a hostel or dormitory, that seems unlikely so I am trying to get confirmation on what you need.
You indicate you want a list of available rooms but your examples refer to matching reservations that overlap. I read this that you want to see a list of rooms that DO NOT have any reservvations that over lap the specified date range.
Seems like a new reservation would have three items of information, start date, end date and number of beds, which can be entered as global fields. Then, when an available room is selected in the portal, the data from the global fields can be combined with the Room ID of the selected room to create a new reservation record. (This can also be used to refine room selection per guest specified criteria. "Room 123 has a jacuzzi bath it's $235 a night instead of $135, would you like to reserve it?")
That make sense?
"I think that's just imprecise language but it reads like you are renting out individual beds instead of rooms. (Bed 1 is occupied but bed 2 in the same room is free so list this room....)"
Yes, this is exactly what I am doing. Single reservation is always for single bed only, and there can be more than one person booked for given room, if there is more than one bed in it.
I am doing a team based reservations, within a pool of rooms, and each person from the team is given a separate reservation for multi-bedded rooms. People from the team arrive and depart at different times, and there can be more people in the team overall than there are rooms (case of more people than rooms showing at once is not part of issue on my end).
The overlaping reservations were the part of the pseudocode for filtering which rooms should not be listed. I was hoping to count the overlaping reservations and compare them to the number of beds. If the number of reservations is equal to number of beds, the room would not be listed for selection.
If I was booking whole rooms, than the filtering would be borderline trival. However, it is the exactly the possibility of having reservations for given date and still having one spot open. which is giving me problems.
Ok. That opens a whole other question about whether a bed is available if the other beds in a room are reserved by a different team, but I'll leave that issue alone for now...
Reproducing the apparent relationships that you have:
RoomTypes::id_type = Rooms::id_type
Rooms::id_room = Reservations::id_room
What I don't see here is a field in your reservations table that indicates how many beds of the specified room have been reserved by that record. Perhaps you have a "numbRooms" field that is part of that field. Otherwise, I don't see any way to determine if a given room does or does not have any available beds.
Yes, the issues of overbooking (as in sending more people at once that there are spots open), and mixing of teams are not part of my problem at the moment.
There is no field for number_of_beds_reserved, since as I mentioned, every reservation is for single bed. Therefore, the number of used bedes for given date is equal to number of reservations that are active on given date. And that brings me back to the problem outlined in the first post, namely "how to count records that fall into given date range, compare the count result to rooms::number_of_beds_from_room_type, and use the result to filter the portal records".
Sorry if my description of the problem is not as clear as it should be, but english is not my primary language.
Apologies for mis-reading your post. I've not perceived the reservation table correctly until, hopefully, now.
I also think that I misunderstood the purpose of the portal. I thought you wanted a list of rooms available, but this sounds like you want a list of rooms that already have a reservation overlapping the one in question but have at least one bed available.
I've not seen it that way as I've not been able to fully imagine why such a list would be useful.
To count bed reservations for a given room for a given date range is not simple as we've found out. I've been posting to another thread here where the user has a similar need for renting out equipment without double booking the same equipment: Equipment QTY Available for a Given Date Range
LaRetta made an interesting suggestion here and I want to investigate that suggestion in light of what we need here in a test file before I go any further.
The issues that make this "messy" are:
THere are three tests you have to make to see if a given reservation overlaps a specified date range.
When you count the overlapping records, care must be taken to keep from counting the same record more than once.
The resulting relationships can become very complex and this can create screen refresh issues.
After some testing, I believe I have been able to get this to work. See if the portal on the ResCheck layout of this demo file does what you want: http://www.4shared.com/file/gGZ8xzCh/BedRentingTest.html
If LaRetta is reading thie, I'm pleased to note that no Refresh Window or even a commit record step was needed to get the portal to update when you specify different date ranges.
This system uses three relationships between rooms and reservations to count all reservations records that overlap the specified dates entered into the global date fields. The global fields are defined in Rooms so that they can be used for these relationships.
EnclosedBedReservations is used to count the reservations where the start date precedes the global start date and the end falls on a date after the global end date. < and >, not < and > are used in this relationships so that no records that match this relationship are also records that match in either of the other two relationships.
It uses this relationship:
Rooms::__pk_RoomID = EnclosedBedReservations::_fk_RoomID AND
Rooms::gStartDate < EnclosedBedReservations::Startdate AND
Rooms::gEndDate > EnclosedBedReservations::EndDate
StartDateBedReservations is used to count the reservations where gStartDate falls on the interval from startDate to endDate.
Rooms::__pk_RoomID = StartDateBedReservations::_fk_RoomID AND
Rooms::gStartDate > StartDateBedReservations::Startdate AND
Rooms::gStartDate < StartDateBedReservations::EndDate
cStartDateResList is used to generate a list of all reservation IDs that meet the criteria of the StartDateBedReservations relationship. We'll use this calculation to exclude any records from the third relationship that meet its criteria:
List ( StartDateBedReservations::__pk_BedResID )
The third relationship, for EndDateBedReservations, then becomes:
Rooms::__pk_RoomID = EndDateBedReservations::_fk_RoomID AND
Rooms::gEndDate > EndDateBedReservations::Startdate AND
Rooms::gEndDate < EndDateBedReservations::EndDate AND
Rooms::cStartDateResList ≠ EndDateBedReservations::__pk_BedResID
To count reservations so that we can determine which rooms have available beds, I defined a summary field, sCount in BedReservations to "count" the __pk_BedResID field. (Selected this field for counting as it is never empty.)
This then makes possible two calculation fields in Rooms:
EnclosedBedReservations::sCount + EndDateBedReservations::sCount + StartDateBedReservations::sCount
RoomTypes::BedCapacity - ( EnclosedBedReservations::sCount + EndDateBedReservations::sCount + StartDateBedReservations::sCount )
Now for a portal filter expression, I used:
Rooms::cBedsAvailable > 0
Had I omitted that filter expression, the same portal would list all rooms and provide a count of available beds for each.
"I also think that I misunderstood the purpose of the portal. I thought you wanted a list of rooms available, but this sounds like you want a list of rooms that already have a reservation overlapping the one in question but have at least one bed available."
Close enough. Exact solution I seek is "that already have a reservation overlapping the one in question but have at least one bed available OR have no reservation made for date range in question", but I should be able to work my way from here. Once again, sorry for not being clear enough with what exactly I am needing.
Thank you very much for all the help you have provided. I will look into given examples, and hopefully be able to understand how they work and, should the need arise, tweak them to my project specific needs.
After I set up and tested the demo file, I found that it lists exactly that. It lists all rooms that have at least one bed available in the specified date range.
I successfully integrated your solution into my system, and as far as I tested, it works perfectly.
Thank you very much for your help, I doubt I would be able to work it out myself.
I've been running two very similar threads on this issue. Another user needed to rent out beds (not rooms) and needed a list of all rooms with at least one bed available for a specified date range. I suggested something there that may update more smoothly than the sum functions I just recommended here.
Define a summary field in the LineItems table as the total of your quantity field. then replace the sum functions in that last expression with three references (one for each relationship) to this summary field. You may find that it updates quicker as you work with the larger numbers of records in your actual database.