I'm working on displaying a weekly schedule and what I want to do is to merge appointments if they are consecutive and have no gaps. For example: I have
7:00AM - 08:00AM AND
03:00PM This is the time schedule of the doctor.
In other words, how to find all the records that have consecutive values for the same doctor and day of the week, and return the minimum value of Start Time and the maximum value of End Time of that consecutive set.
Is it possible to retrieve that data and convert it to
07:00AM - 03:00PM using SQL? If so how?
SQL Server 2008 R2 Here is my sample data:
Try the following query:
;WITH GatherDates AS ( SELECT DocID, StartTime AppointmentTime FROM Appointment UNION ALL SELECT DocID, EndTime FROM Appointment ) ,RemoveDuplicates AS ( SELECT DocID, AppointmentTime, COUNT(*) DuplicateCount FROM GatherDates GROUP BY DocID, AppointmentTime ) ,FindContinuosAppointment AS ( SELECT DocID, AppointmentTime, ROW_NUMBER() OVER(PARTITION BY DocID ORDER BY AppointmentTime ASC) RN FROM RemoveDuplicates WHERE DuplicateCount = 1 ) SELECT ST.DocID, ST.AppointmentTime StartTime, ET.AppointmentTime EndTime FROM FindContinuosAppointment ST INNER JOIN FindContinuosAppointment ET ON ST.DocID = ET.DocID AND ST.RN = ET.RN - 1 AND ST.RN % 2 = 1 AND ET.RN % 2 = 0;
Check my SQL Fiddle link: http://www.sqlfiddle.com/#!3/04b71/1/0
Do let me know your comments.
Hello if you just want the minimum hour and the maximum hour for each doctor for each day then it will be very simple:
Select Doctor,DayOfWeek,Min(StartTime),Max(EndTime) from Schedule Group By Doctor,DayOfWeek
But if you want to take into account possible gaps, then it is not possible at all, you cannot anticipate how many record you will need to compare so you will need an algorithm, not possible to achieve with a SQL query. You can write an store procedure, fetch all the records you want to compare and create the desired output, but it will be kind of slow.
My suggestion here is forget about trying to achieve this on the Database and do the logic of merging appointments on the frontend where you show the schedule. Even if you manage to achieve it on the Database server with a Stored Procedure, your client (the doctor who does not know a thing about software requirements) will ask you accept half an hour appointments and stuff like that will make things harder.