Does anyone have any ideas or suggestions for how I can deliver a time booking application?
Very simply, I am designing an application to replace a paper based car pool booking system.
A single car can be booked from 08:00 to 18:00 in half hour intervals.
Here is an example of a potential row in my booking table:-
ID CAR_ID START END
----
1001 CAR-A 08-JAN-08 09:00 08-JAN-08 15:30.
How can I display this....
08:00 available
08:30 available
09:00 booked
09:30 booked
.
.
.
15:00 booked
15:30 available
16:00 available
16:30 available
.
.
.
I have 2 major issues
1. All the 'available' times do not exist in the table because they have not been booked.
How can I do a select on rows that don't exist?
2. How can I use SQL to highlight that 15:30 is free even though it is recorded in the booking row?
To put it another way, 15:30 is held in the row but as it is the END of the booking, 15:30 onwards is still valid therefore available.
I want if possible to avoid populating a table of 'Time slots' holding 08:00 to 18:00 and then doing an outer join on it but I will if that's the best solution.
I also don't want to just populate the booking table with all the slots for every day, for every car, for the next however-long as again this seems like a poor solution.
I've studied various threads about calendar based applications but I haven't found anything useful (or understandable!) and I am using Apex v2 so the existing calendars I have at my disposal don't fulfil the requirements.
Any suggestions would be most welcome.
If anyone needs more clarification on the issue in order to study it please say so. I have tried to keep it simple.
Anyway, I shall go back to banging my head against the wall untill someone more qualified comes up with something!
Thanks
James
