p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   Pro VB Databases (http://p2p.wrox.com/forumdisplay.php?f=81)

 rahilahmed May 25th, 2004 06:12 AM

Forward booking

I hope someone can give me an idea of how to achieve the followings:
I am working on a forward booking system, the item should be able to be booked for the period it is not booked so potentially the dates could fall between 2(two) booked dates, example item is booked for 12 dec to 13 decemeber and then for 16 dec to 17 dec, then if someone wants to book for available period between 14 and 15 th, they can do that. I ll apreciate any help on the kind of SQL statement or logic.. by the way I am using VBA for excel and access .... Thanks

 tnd June 16th, 2004 06:02 PM

Assuming your table stores the start and end dates in 2 seperate columns then you simply need to make sign of the difference in dates for any row is the same.
Ie
row Start End
1 1/14 1/16
2 1/21 1/25

New request 1/15 - 1/17
So you run datediff (or whatever function in access/excel)
1/14 and 1/15 = 1
1/16 and 1/15 = -1 the signs are different so 1/15 is between 1/14 and 1/16

request 1/18 - 1/19
1/14 and 1/18 = 4
1/16 and 1/18 = 2 then signs are same so 1/18 is outside of 1/14 and 1/16
1/14 and 1/19 = 5
1/16 and 1/19 = 3

As long as the sign is the same any start/end pair then the date you are checking is not in the range.
Do the same for all the previous bookings and you're all set.

 All times are GMT -4. The time now is 07:55 AM.