4 Replies Latest reply on Jan 8, 2008 9:07 AM by Denes Kubicek

    Booking App problems

      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!


        • 1. Re: Booking App problems
          Luis Cabral

          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
          (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.

          • 2. Re: Booking App problems
            Denes Kubicek

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


            to give you an idea how you could do that.

            Denes Kubicek
            • 3. Re: Booking App problems

              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.


              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

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

                Denes Kubicek