Select Query

zed420 · #1 (**permalink**) 08-31-09, 08:57

Hi All
I've managed to get somewhere with the query below with some help but it has a small problem after rigorous testing I found there is a small glitch e.g. Let’s say if I book from 10:00 to 13:00 and someone tries to book it from 08:00 to 21:00 (all day) IT WILL BOOK, meaning it will over lap. I have tried to sort it but failing miserably, anyone with any thoughts
Thanks
Zed

Code:

SELECT b_id, COUNT(*) AS Cnt 
	FROM booking 
	WHERE request_date='$request_date' AND 
	( 
   ($s_time >= s_time AND $s_time < e_time) OR 
   ($e_time > s_time AND $e_time <= e_time) 
	) 
	GROUP BY b_id

r937 · #2 (**permalink**) 09-01-09, 07:17

see similar post: Absences between a date range...

Code:

SELECT COUNT(*) AS Cnt 
  FROM booking 
 WHERE request_date = '$request_date' 
   AND $e_time > s_time 
   AND $s_time < e_time

when Cnt is > 0, that means there is already some booking that overlaps your $s_time::$e_time range

hint: do not split your dates and times into separate columns -- use a single DATETIME column

zed420 · #3 (**permalink**) 09-01-09, 14:07

Thank you sir I saw your post of timeline in a different forum and managed to sort it. That was a great help it's been a week I've been at this query.

Thanks
Zed

Internet Services

Web Development

Digital Marketing

Consumer Tech