Re: Calculating time unused

"David Robley" <robleyd@ozemail.com.au> wrote in message news:MPG.19e4d593ae9256e398982f@news.php.net... > In article <20030930103246.49107.qmail@pb1.pair.com>, shaun@mania.plus.com > says... > > Hi, > > > > I have a table called Bookings which has two important columns; > > Booking_Start_Time and Booking_End_Time. These columns are both of type > > DATETIME. Given any day how can I calculate 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, can this be done with a query > > or do I have to work it out with PHP? > > > > Thanks for your help > > Seems easy enough - just sum the time booked per day and subtract it from > the constant which is the total of available hours, and group by day. > You'll need to calculate the booked time based on existing start/end > values but you should be able to do that in SQL. > > Cheers > -- > Quod subigo farinam > > A: Because it messes up the order in which people normally read text. > Q: Why is top-posting such a bad thing? > A: Top-posting. > Q: What is the most annoying thing on usenet? Thanks for your reply, I am having some odd results with this, just wondering if you could check this query for me: "SELECT COUNT(B.Booking_ID) AS Booking_ID, SUM(8.5 - (DATE_FORMAT(Booking_Start_Date, '%h:%i:%s')) + (DATE_FORMAT(B.Booking_End_Date, '%h:%i:%s'))) AS Available_Hours FROM WMS_Bookings B WHERE B.User_ID = '".$user_id."' AND B.Booking_Status <> '1' AND '".date("Y-m-d", strtotime("$loop3_this_year-$loop3_this_month-$loop3_this_day"))."' = DATE_FORMAT(Booking_Start_Date, '%Y-%m-%d')"; Thanks for your help