Geek Answers Handbook

How to get affordable time for a doctor from today's appointments?

I am working on a project in a company. I am trying to resolve this request but could not.

My tables:

  • 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

  • This is my table timeSchedule:

    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 meanings, but I think this is enough to solve the problem.

I compare patient appointments with this table timeSchedule.

Now suppose that if you pass the parameter doctorIdas 56 and consider today March 23 , then the output should look 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?

+5
source share
1 answer

, timeSchedule.startTime timeSchedule.endTime , - ...: ( , ).

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)
                 )
               )

:

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

, , - , , .

. . .

+4

More articles:


All Articles