0

I need a rails/mysql query to find those clinics which have a given time slot available

clinic.rb
has_many :appointments
id | name 
1 | Clinic A
2 | Clinic B

appointments
belongs_to :clinic
clinic_id | start_time       | end_time         | user_id
1         | 20 Jan 2016 12:00| 20 Jan 2016 13:00| 1
2         | 20 Jan 2016 13:00| 20 Jan 2016 14:00| 1
2         | 20 Jan 2016 16:00| 20 Jan 2016 18:00| 1
1         | 20 Jan 2016 15:00| 20 Jan 2016 18:00| 1

Now the user is going to give a time slot say 20 Jan 2016 13:00 to 13:30

In that time slot, I need to find clinics that are free for exactly that duration Clinic B has an appointment from 13:00 to 14:00 so it is unavailable Clinic A doesn't have any appointment, so it will give me that data

Similarly if the user selects 11:00 - 11:30 he will get both A and B

So given a time range, I need to figure out which clinic (if any) is free during the time

SoWhat
  • 5,564
  • 2
  • 28
  • 59

1 Answers1

1

You should solve this using the range overlap logic. You can find a nice explanation here

Determine Whether Two Date Ranges Overlap

SQL Fiddle Demo

SELECT c.*, a.*
FROM clinic c
LEFT JOIN appoinment a
      ON c.`id` = a.`clinic_id`
     AND `start_time` < '2016-01-20 13:30:00'
     AND `end_time`   > '2016-01-20 13:00:00'
WHERE  `start_time` IS NULL;

SELECT c.*, a.*
FROM clinic c
LEFT JOIN appoinment a
      ON c.`id` = a.`clinic_id`
     AND `start_time` < '2016-01-20 11:30:00'
     AND `end_time`   > '2016-01-20 11:00:00'
WHERE  `start_time` IS NULL;

If you date overlap with any appointment you will have a matching join. Otherwise the time slot is free and you get NULL

Community
  • 1
  • 1
Juan Carlos Oropeza
  • 47,252
  • 12
  • 78
  • 118