Hi there,
I've searched through the forum and cannot find a similar query to my question so am having to post this new thread.
I have a table that is used to store booking details (basically a reservation system) and I would like to base some validation on this table.What I would like to do is before inserting a new record validate that there is availability for the period being requested.
The table has the following columns:
BOOKING_ID
CUSTOMER_ID
DATE_OF_ARRIVAL
NUMBER_OF_NIGHTS
Some example records might be:
BOOKING_ID CUSTOMER_ID DATE_OF_ARRIVAL NUMBER_OF_NIGHTS
------------------------------------------------------------------------------------------------------------
1 201 13-OCT-2010 5
2 202 13-OCT-2010 3
3 203 15-OCT-2010 2
Based on the above I now have the following in terms of reservations per day:
DATE BOOKINGS_COUNT
------------------------------------------------
13-OCT-2010 2
14-OCT-2010 2
15-OCT-2010 3
16-OCT-2010 2
17-OCT-2010 1
Clearly I dont really want to be maintaining a separate table to persist the bookings per day!! So i was wondering how do a write a query that can essentially represent that count per day? I would then use that to validate availability, for example if my maximum reservations per day is 3, if a new customer attempted to book for 15-OCT-2010 for 2 nights the validation would prevent him from doing so as we already have 3 reservations for the 15th.
Any help or advice would be much appreciated.
Kind regards,
stu