+- Hack Forums (https://hackforums.net)
+-- Forum: Tech (https://hackforums.net/forumdisplay.php?fid=444)
+--- Forum: Coding (https://hackforums.net/forumdisplay.php?fid=151)
+---- Forum: Coders Lounge (https://hackforums.net/forumdisplay.php?fid=5)
+---- Thread: [DBMS] Availability Calendar - date search (/showthread.php?tid=4688393)
Availability Calendar - date search - c00perzz - Feb 16, 2015
Hi,
I have the following situation (poor programming post below so be kind!) :
Im building an availability calendar and i want to be able to search which rooms are availalble on a specific date.
TABLE: ROOMS
roomid
roomname
TABLE: BOOKINGS
bookingid
roomid
from
to
ROOM RECORDS
1 | 'BASEBALL'
2 | 'MOZES'
3 | 'CATER'
EXAMPLE #1:
BOOKING RECORDS
1 | 1 | 2015-02-05 | 2015-05-08
2 | 2 | 2015-02-12 | 2015-05-18
3 | 3 | 2015-02-05 | 2015-02-22
The above works fine. The problem is if a room has more than 1 bookings on different dates:
BOOKING RECORDS
1 | 1 | 2015-02-05 | 2015-05-08
2 | 2 | 2015-02-12 | 2015-05-18
3 | 3 | 2015-02-05 | 2015-02-22
4 | 3 | 2015-02-25 | 2015-02-27
On the above example, if i search for rooms between the dates 2015-02-05 to 2015-02-22 it will not show ROW #3 but it will show #4! If a room has booking within those days it does not show it (which is correct). If though that room has booking on another day which does not fall within the SEARCH RANGE it is shown as available!
I basically need help on the logic of this. If a room is booked on the searched dates then it must not show any other rows of it from other dates!
I hope i didnt confuse you 😊
* - szuszi2 - Feb 23, 2015
What is your query? basicly you would want to do this: Spoiler (Click to View)