Results 1 to 3 of 3

Thread: Select Query

  1. #1
    Join Date
    Aug 2009
    Posts
    3

    Unanswered: Select Query

    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    see similar post: http://www.dbforums.com/showthread.php?t=1626870

    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Aug 2009
    Posts
    3
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •