Best way to represent schedule overlap with the data in SQL server table

Question

I have a table with the surgery schedule information for doctors.here is sample data i have in the table. i am trying to get the data out from the table with a extra columns like overlapflag, overlap_percenatge. here is the sample data i have in my table.

create table #Schedule_Test 
(SURG_ID INT , PAT_ID VARCHAR(10),DOC_ID VARCHAR(10),PROC_BEGIN DATETIME,PROC_END DATETIME)

INSERT INTO #Schedule_Test
VALUES(1, 'PAT-01' ,'DOC-01','2015-12-09 07:00:00.000','2015-12-09 07:30:00.000')
     ,(2, 'PAT-02' ,'DOC-01','2015-12-09 07:15:00.000','2015-12-09 07:45:00.000')
     ,(3, 'PAT-03' ,'DOC-01','2015-12-09 08:00:00.000','2015-12-09 08:30:00.000')
     ,(4, 'PAT-04' ,'DOC-01','2015-12-09 08:15:00.000','2015-12-09 08:45:00.000')
     ,(5, 'PAT-05' ,'DOC-01','2015-12-09 09:00:00.000','2015-12-09 09:30:00.000')
     ,(6, 'PAT-06' ,'DOC-01','2015-12-09 11:00:00.000','2015-12-09 12:30:00.000')
     ,(7, 'PAT-07' ,'DOC-01','2015-12-09 11:30:00.000','2015-12-09 12:00:00.000')

If we look at the result set of the query, surg-01 started at 7:00 and ended at 7:30 where as surg-02 starting befor surg-01 ends which means this is overlapping procedure. And sug-03 is not over lapping with with Surg-02 but it is over lapping with the surg-04. So if any surgery overlap with other surgery in a given day then I should mark it as overlap. With my knowledge and some help form the other blogs I got this query.

**( THIS QUERY WILL EXECUTE ONLY IN 2012 OR GREATER VERSION OF SQL SERVER)**


SELECT T.*,
CASE WHEN cat.PAT_ID IS NULL THEN 'N' ELSE 'Y' END AS Overlap_Flag,
CASE WHEN cat.PAT_ID IS NULL THEN '0' ELSE CONCAT(DATEDIFF(MINUTE,T.PROC_BEGIN,cat.PROC_END)/60,'H ',DATEDIFF(MINUTE,T.PROC_BEGIN,cat.PROC_END)%60,' MIN') END AS Overlap_Time
,cat.PAT_ID as Overlapping_with_PAT_ID
FROM #SCHEDULE_TEST T
OUTER APPLY
(  SELECT TOP 1 * 
   FROM #Schedule_Test 
   WHERE T.DOC_ID = DOC_ID 
   AND T.PROC_BEGIN > PROC_BEGIN 
   AND T.PROC_BEGIN < PROC_END
   ORDER BY PROC_BEGIN ) cat

bust this is not exactly what i am looking for . Here is what iam looking for logic for calculating the New columns Overlap_Flg will be true(Y) if there is overlap between the procedures overlap_percentage will be calculated based on overlaptime between the procedures/total procedure time for that patient

SURG_ID PAT_ID  DOC_ID        PROC_BEGIN       PROC_END          Overlap_Flag   Overlap_percentage  Overlapping_with_PAT_ID
1   PAT-01  DOC-01  2015-12-09 07:00:00.000 2015-12-09 07:30:00.000    Y                    50%                  PAT_02
2   PAT-02  DOC-01  2015-12-09 07:15:00.000 2015-12-09 07:45:00.000    Y                    50%                  PAT-01
3   PAT-03  DOC-01  2015-12-09 08:00:00.000 2015-12-09 08:30:00.000    Y                    50%                  PAT-04
4   PAT-04  DOC-01  2015-12-09 08:15:00.000 2015-12-09 08:45:00.000    Y                    50%                  PAT-03
5   PAT-05  DOC-01  2015-12-09 09:00:00.000 2015-12-09 09:30:00.000    N                    0%                    NULL
6   PAT-06  DOC-01  2015-12-09 11:00:00.000 2015-12-09 12:30:00.000    Y                    50%                   PAT-07 
7   PAT-07  DOC-01  2015-12-09 11:30:00.000 2015-12-09 12:00:00.000    Y                    100%                      PAT-06

any HELP will be APPRECIATED

what is `ture(Y)`? And can a surgery 01 overlap with surg-02 and surg-03 at same time? — Juan Carlos Oropeza, Jan 13 '16 at 21:09
Your example is wrong. PAT-05 overlap with PAt-04 because your insert have diferent times — Juan Carlos Oropeza, Jan 13 '16 at 21:17
sorry that is a Typo error for TRUE , Surg-01 can overlap with only surg-02 or surg 03 but not surg-02 AND surg-03 , our system does not allow more than 1 overlap surgery in a give day. — , Jan 13 '16 at 21:17
Now I see frisbee answer have a question. The overlaps occurs if the docA is in both surg1 and surg2 at the same time. But if both surgery are at the same time with different doc isnt overlaps? Your sample data doesnt include that case, but your query suggest is relevant — Juan Carlos Oropeza, Jan 13 '16 at 21:33
there will be multiple DOCTORS , but there will be only ONE Primary physician. I am writing this only for primary physicians. — , Jan 13 '16 at 21:55
Yes but my question still remain... if main doctorA is on both surgery is a overlap, otherwise you can have two surgery at same time with diferent main doctors and is ok? — Juan Carlos Oropeza, Jan 13 '16 at 21:57
yes, that is right if doctorA is on both surgery then it is overlap. two surgery at same time with diferent main doctors is NOT a overlap — , Jan 13 '16 at 22:00

score 0 · Accepted Answer · edited May 23 '17 at 12:23

There is a very easy way to compare if two dates overlaps.

Determine Whether Two Date Ranges Overlap

Using that you can join the table with itself to find if A overlaps with B.

SQL FIDDLE DEMO

SELECT ST1.*,
       CASE WHEN ST2.PAT_ID IS NULL THEN 'N' ELSE 'Y' END AS Overlap_Flag,
       CASE WHEN ST2.PAT_ID IS NULL THEN '0' 
            ELSE CONVERT(nvarchar(10),
                         CONVERT(numeric(10,2),
                            DATEDIFF(MINUTE, 
                                     CASE WHEN ST1.PROC_BEGIN < ST2.PROC_BEGIN 
                                          THEN ST2.PROC_BEGIN
                                          ELSE ST1.PROC_BEGIN
                                     END, 
                                     CASE WHEN ST1.PROC_END < ST2.PROC_END 
                                          THEN ST1.PROC_END
                                          ELSE ST2.PROC_END
                                     END                              
                                    ) * 100.0
                            /
                            DATEDIFF(MINUTE, ST1.PROC_BEGIN, ST1.PROC_END) 
                            )
                     ) + '%'
       END AS Overlap_Time,
       ST2.PAT_ID as Overlapping_with_PAT_ID
FROM Schedule_Test ST1
LEFT JOIN Schedule_Test ST2
       ON ST1.PROC_BEGIN <= ST2.PROC_END
      AND ST1.PROC_END >= ST2.PROC_BEGIN
      AND ST1.SURG_ID <> ST2.SURG_ID
      AND ST1.DOC_ID = ST2.DOC_ID

This will provide if A overlap with B but also if B overlap with A

OUTPUT

| SURG_ID | PAT_ID | DOC_ID |                 PROC_BEGIN |                   PROC_END | Overlap_Flag | Overlap_Time | Overlapping_with_PAT_ID |
|---------|--------|--------|----------------------------|----------------------------|--------------|--------------|-------------------------|
|       1 | PAT-01 | DOC-01 | December, 09 2015 07:00:00 | December, 09 2015 07:30:00 |            Y |       50.00% |                  PAT-02 |
|       2 | PAT-02 | DOC-01 | December, 09 2015 07:15:00 | December, 09 2015 07:45:00 |            Y |       50.00% |                  PAT-01 |
|       3 | PAT-03 | DOC-01 | December, 09 2015 08:00:00 | December, 09 2015 08:30:00 |            Y |       50.00% |                  PAT-04 |
|       4 | PAT-04 | DOC-01 | December, 09 2015 08:15:00 | December, 09 2015 08:45:00 |            Y |       50.00% |                  PAT-03 |
|       5 | PAT-05 | DOC-01 | December, 09 2015 09:00:00 | December, 09 2015 09:30:00 |            N |            0 |                  (null) |
|       6 | PAT-06 | DOC-01 | December, 09 2015 11:00:00 | December, 09 2015 12:30:00 |            Y |       33.33% |                  PAT-07 |
|       7 | PAT-07 | DOC-01 | December, 09 2015 11:30:00 | December, 09 2015 12:00:00 |            Y |      100.00% |                  PAT-06 |

You can fix it if replace last line with

      AND ST1.SURG_ID < ST2.SURG_ID

Also Im returinig * you just have to adapt to return the format you need, but looks like you already know how to do that.

NOTE

This work with older verion of SQL I test it on 2008
If a surgery doesnt overlap with anyone will return null on the last columns

Carols, thanks for your time , i am not able to write the query for returning the Overlap_flag and overlap_percentage. can you please help me with that, query i have written above is not giving me the correct result set. — , Jan 13 '16 at 21:41
I fix the flag, in your previous query you have a string with the time now you want a porcentaje? — Juan Carlos Oropeza, Jan 13 '16 at 21:55
done, but your before last example % is wrong. surgery is 90 min overlaps 30 so is 33% — Juan Carlos Oropeza, Jan 13 '16 at 22:09
You are genius, THANK YOU so much Carlos!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!. There is one extra column i added here to make this question easy to understand that is Surg_id which is not there in my table. i tried run the query with out SURG_ID but getting the duplicates. Can you PLEASE help with this same query without surg_id. — , Jan 13 '16 at 22:21
I just update my question to include a sqlfidle demo and show the percentage with 2 decimals. Just replace the first line `SELECT ST1.*,` with the name of the fields you want. Im going home probably off for couple hours. — Juan Carlos Oropeza, Jan 13 '16 at 22:24
carlos , issue is not with the select statements , i am talking about this joining condition in the query AND ST1.SURG_ID <> ST2.SURG_ID. if i remove this condition i am getting duplicates. — , Jan 13 '16 at 22:39
just use `ROW_NUMBER()` to create a fake_id to replace the missing surg_id. http://sqlfiddle.com/#!6/57f0f/11, but you need some kind of id to make comparasion. — Juan Carlos Oropeza, Jan 13 '16 at 23:32

paparazzo · Answer 2 · 2016-01-13T21:59:12.713

0

SELECT T.*,
       O.surdgID as O.surgID as Overlapping_surgID, 
       CASE WHEN O.surgID IS NULL THEN '0' 
            ELSE DATEDIFF(MINUTE, O.PROC_BEGIN, T.PROC_END) * 100 / DATEDIFF(Minute, T.PROC_BEGIN, O.PROC_END) 
       END AS Overlap_Pct
,cat.PAT_ID as Overlapping_with_PAT_ID
FROM #SCHEDULE_TEST T 
LEFT JOIN #SCHEDULE_TEST O
       on O.docID = t.docID 
      and O.surdID <> t.surdID  
      and T.PROC_END > O.PROC_BEGIN  
      and T.PROC_END < O.PROC_END  -- if in order I don't think you need this 
                                   -- and use O.surdID > t.surdID

edited Jan 13 '16 at 21:59

answered Jan 13 '16 at 21:27

paparazzo

44,497
23
105
176

1

Your query miss the alias for the right dates. And your comparation are wrong. You can check mine and the link proving that is the correct formula. – Juan Carlos Oropeza Jan 13 '16 at 21:30
@JuanCarlosOropeza Yes I am still (trying) to clean up – paparazzo Jan 13 '16 at 21:34

Best way to represent schedule overlap with the data in SQL server table

2 Answers2