If I understand you correctly, you need a query that will return for 1
user's available hours for the next 10 days. (Since you didn't use any of
the new datetime functions that were added recently to MySQL, I assume you
are using a version < 4.x)
SELECT User_ID
, DATE_FORMAT(Booking_End_Date, "%Y-%m-%d") as AvailDate
, 8.5 - (
SUM(
(0.0 + UNIX_TIMESTAMP(Booking_End_Date))
- (0.0 + UNIX_TIMESTAMP(Booking_Start_Date))
) /3600
) AS Available_Hours
FROM Bookings b
WHERE b.User_ID = '610'
AND Booking_Start_Date >= '2004-07-08'
AND Booking_End_Date < '2004-07-19'
GROUP BY User_ID, DATE_FORMAT(Booking_End_Date, "%Y-%m-%d")
NOTES:
The "0.0+" math converts the UNIX_TIMESTAMP() results to a signed floating
point value. Divide by 3600 to convert total seconds to hours. I changed
the WHERE clause to select those bookings that start anytime on or after
your first day and end sometime during the last day (that's why I said <
the 11th day). Be aware that if you have any bookings that start on one day
and end on another, this calculation will fail miserably. Hopefully, though
it helps you to see how many different ways there are of looking at the
issue.
This query will only return rows for dates where a booking entry exists. If
in that 10 day span, you have a completely open day, no row will exist that
will trigger a calculation for that day so no available hours will be
returned. You can work around this by making either a "dummy" booking (a
booking for 0 elapsed time sometime during that day) or by INNER JOINING a
temporary table of Dates that cover the range you are curious in or by
specifically testing for a list of specific dates in a more complex WHERE
clause.
If you use the "dummy" booking method of tagging days as "bookable", you
could also change the query to look like this:
SELECT User_ID
, DATE_FORMAT(Booking_End_Date, "%Y-%m-%d") as AvailDate
, 8.5 - (
SUM(
(0.0 + UNIX_TIMESTAMP(Booking_End_Date))
- (0.0 + UNIX_TIMESTAMP(Booking_Start_Date))
) /3600
) AS Available_Hours
FROM Bookings b
WHERE b.User_ID = '610'
AND Booking_Start_Date >= '2004-07-08'
GROUP BY User_ID, DATE_FORMAT(Booking_End_Date, "%Y-%m-%d")
ORDER BY DATE_FORMAT(Booking_End_Date, "%Y-%m-%d")
LIMIT 10
Which will return the users available hours during the next 10 available
booking dates (on or after 2004-07-08) regardless of their calendar
sequence. That way if a user is NOT available for bookings (because they
were on vacation...) the absence of a "dummy" entry would not list those
days as "available" in the results.
Yours,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
|---------+----------------------------->
| | "shaun thornburgh"|
| | <[EMAIL PROTECTED]|
| | otmail.com> |
| | |
| | 07/09/2004 04:00 |
| | PM |
| | |
|---------+----------------------------->
>--------------------------------------------------------------------------------------------------------------------------------|
|
|
| To: [EMAIL PROTECTED]
|
| cc:
|
| Fax to:
|
| Subject: Help with a Date Query Please!
|
>--------------------------------------------------------------------------------------------------------------------------------|
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 ( '2004-07-08' < DATE_FORMAT(Booking_Start_Date, "%Y-%m-%d")
OR '2004-07-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 and then for each user so 10 users = 100 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?
Thanks for your help
_________________________________________________________________
It's fast, it's easy and it's free. Get MSN Messenger today!
http://www.msn.co.uk/messenger
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
