Hi,
I would do this in the application layer something like this:
$list_of_days_you_want_to_look_at = ('mon','tue','wed'......); Foreach ($day
in $list_of_days_you_want_to_look_at) {
use a similar query to below but geared to only look at $day instead; }
You should get an output like this:
Mon: 0 hrs
Tue: 3.5 hrs
Wed: .....
Hope this helps,
Cheers,
Andrew
-----Original Message-----
From: shaun thornburgh [mailto:[EMAIL PROTECTED]
Sent: Monday 06 October 2003 20:29
To: [EMAIL PROTECTED]
Subject: Help With a DATETIME Query
Hi,
I have a table called Bookings which has two important columns;
Booking_Start_Date and Booking_End_Date. These columns are both of type
DATETIME. The following query calculates how many hours are available
between the hours of 09.00 and 17.30 so a user can see at a glance how many
hours they have unbooked on a particular day (i.e. 8.5 hours less the time
of any bookings on that day). However, when a booking spans more than one
day the query doesn't work, for example if a user has a booking that starts
on day one at 09.00 and ends at 14.30 on the next day, the query returns 3.5
hours for both days. Any help here would be greatly appreciated.
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 WMS_Bookings B
WHERE B.User_ID = '16'
AND B.Booking_Status <> '1'
AND NOT ( '2003-10-07' < DATE_FORMAT(Booking_Start_Date, "%Y-%m-%d") OR
'2003-10-07' > DATE_FORMAT(Booking_End_Date, "%Y-%m-%d") )
Thanks for your help
_________________________________________________________________
On the move? Get Hotmail on your mobile phone http://www.msn.co.uk/msnmobile
--
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]
