"Shaun" <shaun@mania.plus.com> wrote in message 20031003105321.14234.qmail@pb1.pair.com">news:20031003105321.14234.qmail@pb1.pair.com... > > "David Robley" <robleyd@ozemail.com.au> wrote in message > MPG.19e4d593ae9256e398982f@news.php.net">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 Fixed it :) $query = "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 = '".$user_id."' AND DATE_FORMAT(Booking_Start_Date, '%Y-%m-%d') = '".date("Y-m-d", strtotime("$year-$month-$day"))."'"; -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
