Gustav Brock
gustav at cactus.dk
Fri Feb 14 04:26:01 CST 2003
Hi Darren
> I have been playing with your solution for 2 days and I can't work it out,
> (Sorry)
> It seems to be giving odd results.
> EG if I put in a booking into tbBooking with an Interval of "WW" with
> DateStart at 1/1/2003 and DateEnd 31/01/2003, With the tbzMultiply
> table going from 1 to 5 (Max no of weeks in a month) then I get the
> expected results. (8th, 15th, 22nd and 29th Jan) - Excellent
> If I change the multiplier to say 26 and change the DateEnd to 30/06/2003 -
> I would expect to see every Saturday between January 1st and June
> 30th 2003. But I get results that don't make sense. All of Feb is
> omitted for example and one would expect there to be around 21 - 23
> occurrences of each Saturday in that 6 month range (1/1/2003 Being a
> Saturday) I actually get 19 (22nd of Jan is missing, all Feb Dates
> are missing 1st March is missing.
> Then the next dates for March are 12,19th 26th (They're all Wednesdays)
> Every date from there on is OK except they are all Wednesdays not
> Saturdays.
Well, strange calendar you have down there - with 1-1-2003 as Saturday
where it anywhere else is Wednesday!
As for the missing dates I think it must be due to either missing
entries in the tbzMultiply table (check that it contains every integer
from 1 to, say, 100) or your date fields not being assigned data type
date/time.
These are the dates I get here (all Wednesdays):
DatePeriod
08-01-2003
15-01-2003
22-01-2003
29-01-2003
05-02-2003
12-02-2003
19-02-2003
26-02-2003
05-03-2003
12-03-2003
19-03-2003
26-03-2003
02-04-2003
09-04-2003
16-04-2003
23-04-2003
30-04-2003
07-05-2003
14-05-2003
21-05-2003
28-05-2003
04-06-2003
11-06-2003
18-06-2003
25-06-2003
02-07-2003
This shows that if you wish to include 1-1-2003 in your list, 0 should
be added to table tbzMultipy, and if you wish to limit the dates to
those within your date interval you should add this to your Where
statement:
AND (DateAdd([Interval],[Factor],[DateStart]) <= [DateEnd])
to the query mentioned in my original post.
/gustav
>> How would I show a booking that goes from 8:00am to 11:00am to
>> show up in not just 8:00am slot but also 9:00am and 10:00am slot??
>> So it is visible to the user that 9 and 10 am are NOT free.
>> (Yes I do capture the end time of the booking)
> One option is to create a query that generates all your 168 available
> time slots for a week from today.
> Again you can use a Cartesian (multiplying) query for this having the
> table tbzMultiply (one column with integers) mentioned in my previous
> post:
> <SQL>
> SELECT
> DateAdd("h",[aliHour]![Factor]-1,DateAdd("d",[aliDay].[Factor]-1,Date()))
> AS Slot168
> FROM
> tbzMultiply AS aliDay,
> tbzMultiply AS aliHour
> WHERE
> ((aliDay.Factor Between 1 And 7)
> AND
> (aliHour.Factor Between 1 And 24));
> </SQL>
> Save this as qdyBookingSlots.
> Name the query from my previous post selecting the bookings:
> qdy BookingApp
> Now, create a crosstab query joining these:
> <SQL>
> TRANSFORM
> First(qdyBookingApp.ID) AS FørsteOfID
> SELECT
> Format([Slot168],"hh:nn") AS Hours
> FROM
> qdyBookingSlots
> LEFT JOIN
> qdyBookingApp
> ON
> qdyBookingSlots.Slot168 = qdyBookingApp.DatePeriod
> GROUP BY
> Format([Slot168],"hh:nn")
> PIVOT
> Format([Slot168],"yyyy/mm/dd");
> </SQL>
> You amy save this as qdyBookingSlotsUsed.
> This will output a 7 x 24 grid with showing an ID for those time slots
> where a booking exists.
> Note the sorting. If you format the date as dd/mm/yyyy you'll get into
> trouble for grids crossing a month end.