当前位置: 动力学知识库 > 问答 > 编程问答 >

sql - How to find possible consecutive values and return the minimum and the maximum values of that series?

问题描述:

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 have7:00AM - 08:00AM AND 08:00AM - 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?

I'm using 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.

分享给朋友:
您可能感兴趣的文章:
随机阅读: