Shaun Not a 100 % sure if this is what you're looking for or if somebody's already suggested it.... also do not know how practical it is for your application
If a booking spans more than one day (e.g. two) split it into two days - and write two records to the table.... one for each day....this fits with the spirit of your application....a user is going to be interested in free time per day surely.... The problem with this is if you use a booking id which serves as a primary key and will therefore not not allow duplicates..but if that's the case, then there are workarounds for that too... Let me know if I'm warm....:) Rory McKinley Nebula Solutions + 27 82 857 2391 [EMAIL PROTECTED] "There are 10 kinds of people in this world, those who understand binary and those who don't" (Unknown) ----- Original Message ----- From: "shaun thornburgh" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Friday, October 10, 2003 12:37 PM Subject: Help with a DATETIME Query PLEASE! > 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]
