I've worked on applications like this one in a court scheduling application. Since trials can run several days, this was an issue I had to deal with, too. The solution I set up was to have a table like your "bookings" table, then have another table containing available time slots for each eligible date. At the time a booking was created, my application then reserved appropriate time slots in the adjacent table for that particular booking row. The key is that the timeslots table had rows for each day involved and made subsequent queries much easier and more accurate.
This one is my .02... :-) -----Original Message----- From: shaun thornburgh [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 07, 2003 3:51 PM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: RE: Help With a DATETIME Query Thanks for your reply, but its not an option! >From: "Dan Greene" <[EMAIL PROTECTED]> >To: "shaun thornburgh" <[EMAIL PROTECTED]>, ><[EMAIL PROTECTED]> >Subject: RE: Help With a DATETIME Query >Date: Tue, 7 Oct 2003 16:41:04 -0400 > >I know it's not the answer you're looking for... :( but dealing with >overnights has caused me so much aggravation in past apps I've written, >I've tended to make the client create two (or more) 'bookings' for the >covered time... don't know if it's an option for you, but it's my >$0.02..... > > > > > -----Original Message----- > > From: shaun thornburgh [mailto:[EMAIL PROTECTED] > > Sent: Tuesday, October 07, 2003 4:33 PM > > 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. The query is run for each day i.e day 1, > > day 2.... day > > 10. > > > > 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") ) > > > > _________________________________________________________________ > > 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] > _________________________________________________________________ Use MSN Messenger to send music and pics to your friends 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]
