I'm not sure if there is a built-in, but what I usually do for similar things is to use general purpose table to force iteration. E.g. create a table called ITERATE with one column, x, and populate with values 0,1,2,3,4,... -- in your case up to 10 -- and index the column.
Then it's easy - I only show the date field here:
select DATE_ADD(B.Booking_Start_Date,interval it.x day) from Bookings B,iterate it where B.User_ID = '610' and it.x >= 1 and it.x <= 10 group by DATE_ADD(B.Booking_Start_Date,interval it.x day)
Regards, John
-----Original Message-----
> From: shaun thornburgh
> Hi,
>
> I have a table called Bookings which holds start times and end times for
> appointments, these are held in Booking_Start_Date and Booking_End_Date. I
> have a page on my site that runs a query to produce a grid to show
> availiability per day for the next ten days for each user of the system.
> Users work 8.5 hours a day and the query shows how many hours available the
> user has on that day:
>
> SELECT 8.5 - (SUM(((DATE_FORMAT(B.Booking_End_Date, '%k') * 60 ) +
> DATE_FORMAT(B.Booking_End_Date, '%i')) -
> ((DATE_FORMAT(B.Booking_Start_Date, '%k') * 60 ) +
> DATE_FORMAT(B.Booking_Start_Date, '%i'))) / 60) AS Available_Hours
> FROM Bookings B WHERE B.User_ID = '610'
> AND NOT ( '2005-04-08' < DATE_FORMAT(Booking_Start_Date, "%Y-%m-%d")
> OR '2005-04-08' > DATE_FORMAT(Booking_End_Date, "%Y-%m-%d") )
>
> The problem here is I have to do this query to produce a result for each
> cell(date) and then for each user so 100 users = 1000 queries to load the
> page!
>
> Is there a way to produce the result so that I only need one query per user
> so it groups the result by day for the next ten days?
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
