cdsatrian

Free Slot Time

Jun 18th, 2013
110
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
MySQL 0.80 KB | None | 0 0
  1. SELECT a.end AS free_after
  2. FROM bookings a
  3. WHERE NOT EXISTS (
  4.   SELECT 1
  5.   FROM bookings b
  6.   WHERE b.start BETWEEN a.end AND a.end + INTERVAL your_duration HOURS
  7. )
  8. AND a.end BETWEEN start_of_search_window AND end_of_search_window;
  9.  
  10. #you just need to supply values for your_duration (integer), start_of_search_window (date time) and #end_of_search_window (date time).
  11.  
  12. #And if you want bells and whistles....
  13.  
  14. SELECT free_from, free_until
  15. FROM (
  16. SELECT a.end AS free_from,
  17. (SELECT MIN(c.start)
  18.  FROM bookings c
  19.  WHERE c.start>a.end) as free_until
  20. FROM bookings a
  21. WHERE NOT EXISTS (
  22.   SELECT 1
  23.   FROM bookings b
  24.   WHERE b.start BETWEEN a.end AND a.end + INTERVAL your_duration HOURS
  25. )
  26. AND a.end BETWEEN start_of_search_window AND end_of_search_window
  27. )
  28. ORDER BY free_until-free_from
  29. LIMIT 0,3;
Add Comment
Please, Sign In to add comment