OTN Discussion Forums : Booking App problems ...

Reply to this Thread

Search Forum

Back to Thread List

Replies: 4 - Pages: 1 - Last Post: Jan 8, 2008 1:07 AM Last Post By: Denes Kubicek

JTH

Posts: 27
Registered: 08/17/06

Booking App problems
Posted: Jan 7, 2008 7:32 AM

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

Luis Cabral

Posts: 578
Registered: 12/08/05

Re: Booking App problems
Posted: Jan 7, 2008 8:20 AM

in response to: JTH

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

Denes Kubicek

Posts: 4,839
Registered: 06/22/00

Re: Booking App problems
Posted: Jan 7, 2008 9:01 AM

in response to: JTH

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

JTH

Posts: 27
Registered: 08/17/06

Re: Booking App problems
Posted: Jan 8, 2008 12:58 AM

in response to: Luis Cabral

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.

Denes Kubicek

Posts: 4,839
Registered: 06/22/00

Re: Booking App problems
Posted: Jan 8, 2008 1:07 AM

in response to: JTH

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

Pages: 1

Back to Thread List

Legend
	Guru : 2500 - 1000000 pts
	Expert : 1000 - 2499 pts
	Pro : 500 - 999 pts
	Journeyman : 200 - 499 pts
	Newbie : 0 - 199 pts
	Oracle ACE Director
	Oracle ACE Member
	Oracle Employee ACE
	Helpful Answer (5 pts)
	Correct Answer (10 pts)