I am working on a project in a company. I am trying to solve this query but I could not.
My tables are:
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 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?
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With