4 Replies Latest reply: Jan 8, 2008 3:07 AM by Denes Kubicek RSS

    Booking App problems

    528661
      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
          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
            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
              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
                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
                -------------------------------------------------------------------