dates - vacancies and bookings

[Brian O'Gorman]

I have:
a) a calendar table (tblCalendar) holding consecutive dates.
b) a vacancies table (tblVacancies) holding start and end dates of
vacancies
c) a bookings table (tblBookings holding start and end dates of each
booking for a given vacancy.

Can anyone tell me how I can generate, with queries, start and end
dates for the vacant periods of a given vacancy? e.g.

tblVacancy (for a given vacancy) holds: fldStart = 10/1/04, fldEnd =
15/3/04
tblBookings holds, say, three bookings (periods where the vacancy has
been filled)
: fldStart = 20/1/04, fldEnd =
25/1/04
fldStart = 7/2/04, fldEnd =
9/2/04
fldStart = 19/2/04, fldEnd =
25/2/04

I want a query which will produce (the periods during which the
vacancy is still vacant:
fldStart = 10/1/04, fldEnd =
19/1/04
fldStart = 26/1/04, fldEnd =
6/2/04
fldStart = 10/2/04, fldEnd =
18/2/04
fldStart = 26/2/04, fldEnd =
15/3/04

Would be most grateful for anyone's help.
Brian

[fp]

Someone smarter than me may have this one in the can but I can give you my
take on it.

Consider changing the start/end date to start date and duration, like number
of days. You also need to know the rules around inclusion of the end points.
Do you count both start and end dates or do you only count the start date,
end date or neither? Consider a hotel system. You check in after 2pm and
check out by 11am. The first day is counted and the last is not. This is
traditional in the hotel industry and can be applied to other applications
as well.

Off the top of my head you may want to create a class for a booking that
contains the date and duration. You will also need some logic to string
these together to compare the start/duration and come up with any overlaps
and gaps. What you would end up with is a collection of classes that are
either booked or vacant. You can still do this without classes by doing
query for all records and looping through them looking for the end of one
period and the start of the next and calculating the difference.

I did one of these quite a while ago and from what I remember it was quite
complicated by some outside factors, but it still took me a few weeks to
work out all the details and to develop tests to check the logic.

--
******************************
Fred Parker
Lynn Consulting Group, L.L.C.
http://www.lynnconsultinggroup.com
******************************

[Brian O'Gorman]

Hi,
Thanks for your reply.
Would rather not get involved in classes - probably a steep learning
curve for me!!
However, your suggestion of looping through all the records is a good
one, presumably using vb.
Understand your point re counting the first/end days.
Thanks again for your help
Brian

"fp" <et****************@hotmail.com> wrote in message news:<9a******************@nwrddc01.gnilink.net>.. .

Someone smarter than me may have this one in the can but I can give you my
take on it.

Consider changing the start/end date to start date and duration, like number
of days. You also need to know the rules around inclusion of the end points.
Do you count both start and end dates or do you only count the start date,
end date or neither? Consider a hotel system. You check in after 2pm and
check out by 11am. The first day is counted and the last is not. This is
traditional in the hotel industry and can be applied to other applications
as well.

Off the top of my head you may want to create a class for a booking that
contains the date and duration. You will also need some logic to string
these together to compare the start/duration and come up with any overlaps
and gaps. What you would end up with is a collection of classes that are
either booked or vacant. You can still do this without classes by doing
query for all records and looping through them looking for the end of one
period and the start of the next and calculating the difference.

I did one of these quite a while ago and from what I remember it was quite
complicated by some outside factors, but it still took me a few weeks to
work out all the details and to develop tests to check the logic.