How to get available time for doctor from todays appointments?

Question

I am working on a project in a company. I am trying to solve this query but I could not.

My tables are:

1. Appointments:

   doctorId  patientId  patientName  fromDateTime           toDateTime
   --------  ---------  -----------  ---------------------  ---------------------
   56        1          fghfgh       3/23/2012 12:15:00 PM  3/23/2012 01:15:00 PM
   56        2          asdadf       3/23/2012 01:15:00 PM  3/23/2012 02:15:00 PM
   56        3          werwr        3/23/2012 09:15:00 AM  3/23/2012 10:15:00 AM
   57        4          uiyui        3/23/2012 09:15:00 AM  3/23/2012 10:15:00 AM
   57        5          asdad        3/23/2012 01:15:00 PM  3/23/2012 02:15:00 PM
   

2. This is my timeSchedule table:

   id  startTime     endTime
   --  ------------  ------------
   1   08:00:00.000  09:00:00.000
   2   09:00:00.000  10:00:00.000
   3   11:00:00.000  12:00:00.000
   4   12:00:00.000  13:00:00.000
   5   13:00:00.000  14:00:00.000
   6   14:00:00.000  15:00:00.000
   7   15:00:00.000  16:00:00.000
   

   Actually there are more values but I think these are enough to solve the problem.

I am comparing patient appointments with this timeSchedule table.

Now suppose if I pass parameter doctorId as 56 and consider today is 23 March then output should be like this:

id  startTime  endTime
--  ---------  --------
1   08:00 AM   09:00 AM
3   11:00 AM   12:00 PM
6   02:00 PM   03:00 PM
7   03:00 PM   04:00 PM

How can I achieve the above result?

Answer 1

Assuming that timeSchedule.startTime and timeSchedule.endTime are both Time data types then it would be something like this...: (if not, you could cast as such).

DECLARE @pDoctorID Int = 56
DECLARE @pDate Date = '3/23/2012'

SELECT * FROM timeSchedule WHERE 
NOT Exists (
                SELECT doctorid FROM Appointments 
                WHERE doctorid = @pDoctorID AND 
                CAST(fromDatetime as Date) = @pDate AND  
                (
                  (CAST(fromDatetime as Time) >= timeSchedule.startTime AND
                  CAST(fromDatetime as Time) <= timeSchedule.endTime)
                  OR
                  (CAST(toDatetime as Time) >= timeSchedule.startTime AND
                  CAST(toDatetime as Time) <= timeSchedule.endTime)
                  OR
                  (CAST(toDatetime as Time) <= timeSchedule.startTime AND
                  CAST(fromDatetime as Time) >= timeSchedule.endTime)
                 )
               )

Which with your sample data returns this:

1 | 08:00:00.00 | 09:00:00.00

4 | 11:00:00.00 | 12:00:00.00

8 | 15:00:00.00 | 16:00:00.00

In essence the query is saying give me any appointment for this doctor where existing appoints do not start or end between the time frames, or start before and end after any of the time slots defined by the timeSchedule table.

Formatting the return times is also a simple matter. See the table in this link for all your options.