Can this be done in a 'single pass' query?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am handling the bookings for a tennis court.

Each member is allowed to book up to 3 courts in any 7 day period BUT NO
MORE THAN THAT! The maximum period 'ahead' of 'today' that they can book is
also 7 days. This limits our interest to an ABSOLUTE maximum of 7 days ahead
and 7 days prior to today's date

The court times (slots) is held in a table which shows the date and time of
ALL the slots available up to 7 days into the future. A member has booked a
slot when their RefNo is placed in a specific 'slot'. Each 'slot' is 1 hour
in length and all the 'slots' are held in a datetime field/variable. No one
can book any court more than 7 days into the future.

I need a SINGLE (for speed purposes) query (SQL) which returns the NEXT
time/date the member is allowed to play.

The rules are:

If there are 0, 1, or 2 courts booked to a single member within in the 7
days PRIOR to the most-future booked 'slot', they can book at anytime/date up
to 7 days in the future.

If they already HAVE 3 courts booked to them within 7 days of the
most-future booked slot, at what time/date can they NEXT be allowed to book a
court? So, if they have 3 slots booked (e.g. 10am Tues, 3pm Weds and 4pm
Thursday), they will ONLY be allowed to play after 10.am NEXT Tuesday.

Can anything this complex be held in a single query/SQL?

ian
 
Hi Ian,

It may be possible for this to be done in a single query using multiple
nested subqueries (which, to a purist, is not a 'single' query), but I
suspect not.

However, it can be done using a function.
If the table has appropriate indexing, a function should be able to provide
more than adequate response times.

If you need help with a suitable function, reply to this post

Chris
 
You must have guessed! Yes PleEEEEEEEase, I DO need help! My speciality is
FoxPro and I am being forced into using Access for one specific 'output'.

I have no idea how to write a function in Access. The ideal for me would be
to 'return' with the 'next acceptable playing slot'. I can process that
answer if it's returned in that format. I.e.---If that time is prior to
'NOW', then they CAN book any slot from 'NOW' into the future, if it's not
prior to 'NOW', they can book any slot after that 'returned' time.

We can, of course, index the table in any manner you see fit. I would
suggest we index it on the court 'slots'. For ease, those slots are can be
referenced by their DateTime order AND an additional field which is the 'slot
number' which is a sequentially unique ascending list from 1 to X.

T.I.A.
Ian
 
Ok, I'll need the structure of your Booking table: table name, field names
and data types.
The function will need to be passed the "RefNo" of the member, and will
return the primary key of the next available booking, or zero for "none spare"
 
You are a Saint, Chris!

I'll write in in FULL details as I cannot STAND ambiguity in programming.
The concept is simple but the explanation is long:-

Ok. There are effectively two tables, the Members table and the Booking
table. I have simplified both to the essentials for this discussion.

The two tables are as follows:

Members
*******
The Members table hold the Name and RefNo of each member, one record for
each member:-
Members.MeRefNo – Number- unique incremental field for Member Refno
Members.Mename – Character – name of member

Booking
*********
It has a record for each possible booking slot for the entire month/year/or
whatever.

Booking.BoRefNo – Number- unique incremental Ref. No for this ‘slot’
Booking.BoStartTim – Date/Time –start time for the court slot
Booking.MeRefNo - Number – Reference number of the Member who is booking/has
booked the slot

If the MeRefNo field is empty, it’s not booked; if it is has an MeRefNo
within it, that slot is booked to the members whose MeRefNo that is.

The table is ‘naturally’ in Ascending order with the oldest data/time at the
‘start’ of the table.

The ‘rules’ are
*****************
No member can book a court more than 7 days ahead
No member can book a slot for a time that has already passed
No member can have MORE than 3 bookings within a 7 day period. We want to do
that by preventing them from being able to book a slot within that period but
allow them to book any the slots after the date/time when the restriction
expires

Needs
*****
Our ‘needs’ use the concept of the ‘most future’ slot booked by this member.
This is that last date/time within the table when this member has booked a
slot. In practice, this is the FIRST time we encounter this member’s RefNo
(MeRefNo) when reading the Booking table in DESCENDING order. That slot may
be in the future or in the past. If it’s further back in time that 7 days, we
don’t care about it.

We need a 'function' that only returns the date/time on which the member can
NEXT book a slot. Obviously, if they have less than 3 slots booked within 7
days of ‘NOW’ in either direction, they can book from ‘NOW’ onwards. Thus the
time Date/Time returned will be ‘NOW’.

If we read backward from the ‘most future’ slot that this member has booked,
and we find that there are 3 slots booked which are within 7 days of that
‘most future’ slot, we want to know the date/time EXACTLY 7 days from that
EARLIEST slot of the three. Thus, if the member played at 9.00am last
Tuesday, is booked to play at 10.00 tomorrow and is booked to play at 11.00
on Friday, the first time that he can book his NEXT game will be 9.00.am NEXT
Tuesday. (7 days from the earliest date/timed slot of the three)

As I said, I can write that function in FoxPro but I can’t even BEGIN to
work it out in Access!

Thanks in anticipation.

A VERY grateful Ian!!!
 
Hi Ian,

As there may be multiple messages before perfection is achieved, perhpas we
should revert to direct email.
We can post the final result should anyone be interested.
Some more questions
I see no reference to court numbers. Is there only one court?
How many slots are there per day? Do you assign all 24, or a smaller number?

Reply to
Chris
at
mercury-projects
dot
co
dot
nz

Chris
 
Yes, I am writing to you, directly but would be DELIGHTED to post the result
when completed!

Ian
 
Back
Top