This discussion is archived
4 Replies Latest reply: Jan 8, 2008 1:07 AM by Denes Kubicek RSS

Booking App problems

528661 Newbie
Currently Being Moderated
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
  • 1. Re: Booking App problems
    Luis Cabral Pro
    Currently Being Moderated
    Hello

    That seems an interesting project. You can use this query to generate all the 30min slices you need, for a specific day (in this example, today):
    select times.t
    from
    (select to_char(trunc(sysdate) + (9/24) + (level-1)*30/24/60, 'dd/mm/yyyy hh24:mi') t
    from dual 
    connect by level <= 18) times
    You can then easily join it to your booking table to identify what slices have been booked or not (it is just a case of meddling with the join clause to solve problem #2).

    I hope this helps.

    Luis
  • 2. Re: Booking App problems
    Denes Kubicek Oracle ACE Director
    Currently Being Moderated
    James,

    there are many ways to solve this problem. I created an example in my demo application

    http://htmldb.oracle.com/pls/otn/f?p=31517:158

    to give you an idea how you could do that.

    Denes Kubicek
    -------------------------------------------------------------------
    http://deneskubicek.blogspot.com/
    http://www.opal-consulting.de/apex/f?p=107:7
    http://htmldb.oracle.com/pls/otn/f?p=31517:1
    -------------------------------------------------------------------
  • 3. Re: Booking App problems
    528661 Newbie
    Currently Being Moderated
    Luis,

    thanks very much. I had something similar that was doing(almost) the same thing but your solution is much more straight forward.

    I should be ok now I think.

    Denes,

    thanks to you also. Your Apex examples site is a regular point of reference for me. Is the calendar II a new addition as I can't believe I missed it?!

    Anyhoo, thanks guys.
  • 4. Re: Booking App problems
    Denes Kubicek Oracle ACE Director
    Currently Being Moderated
    Luis,

    It is a new page I created to show how to solve your problem.

    Denes Kubicek
    -------------------------------------------------------------------
    http://deneskubicek.blogspot.com/
    http://www.opal-consulting.de/apex/f?p=107:7
    http://htmldb.oracle.com/pls/otn/f?p=31517:1
    -------------------------------------------------------------------