如何查询sql中的非intersect数据和最新的intersect数据？

Question

我有一个表，它是登录病人的集合条目。

   patient, room,   startDate,       endDate
   -------------------------------------------
    0056    001     2012-05-30      2012-05-30 
    0056    001     2013-08-01      2014-09-01 
    0056    001     2013-08-01      2014-09-02
    0056    001     2014-08-01      2014-09-03 
    0056    001     2015-08-01      2016-01-01 
    0056    112     2016-03-01      2017-02-28 
    0005    001     2013-05-01      2014-04-30 
    0005    006     2013-05-01      2015-03-27 

如何创建一个查询，为我提供非互联的dateStart和dateEnd，以及病人的最新日期？

其结果应该是：

   patient, room,   startDate,       endDate
   -------------------------------------------
    0056    001     2012-05-30      2012-05-30 
    0056    001     2013-08-01      2014-09-03 
    0056    001     2015-08-01      2016-01-01 
    0056    112     2016-03-01      2017-02-28 
    0005    001     2013-05-01      2014-04-30 
    0005    006     2013-05-01      2015-03-27 

之所以选择Row 1，是因为它是startDate，而endDate不在row 2,3,and 4之间。然而，Row 2、row 3和row 4是相交的。所以，它只是得到了最新的endDate

我尝试过的查询：

SELECT 
    patient
    ,room
    ,startDate
    ,endDate
FROM T_PATIENT 
GROUP BY 
    patient
    ,room
    ,startDate
    ,endDate

Answer 1

您可以使用OUTER APPLY来分类重叠记录，并确定相应的“开始-结束”重叠期：

SELECT patient, room, startDate, endDate, min_start, max_end
FROM T_PATIENT AS t1  
OUTER APPLY (
   SELECT MIN(startDate) AS min_start, MAX(endDate) AS max_end
   FROM T_PATIENT AS t2
   WHERE t1.patient = t2.patient AND 
         t1.startDate <= t2.endDate AND 
         t1.endDate >= t2.startDate
) AS c

输出：

patient room  startDate   endDate     min_start   max_end
------------------------------------------------------------
56      1     2012-05-30  2012-05-30  2012-05-30  2012-05-30 
56      1     2013-08-01  2014-09-01  2013-08-01  2014-09-03 
56      1     2013-08-01  2014-09-02  2013-08-01  2014-09-03 
56      1     2014-08-01  2014-09-03  2013-08-01  2014-09-03 
56      1     2015-08-01  2016-01-01  2015-08-01  2016-01-01 
56      112   2016-03-01  2017-02-28  2016-03-01  2017-02-28 
5       1     2013-05-01  2014-04-30  2013-05-01  2015-03-27 
5       6     2013-05-01  2015-03-27  2013-05-01  2015-03-27

现在可以将分组应用于上面的结果，以便合并重叠的记录。

Answer 2

SELECT * FROM T_PATIENT  T 
WHERE NOT EXISTS  (
SELECT * FROM T_PATIENT T1 WHERE 
( ( (T.STARTDATE  <= T1.STARTDATE   AND T.ENDDATE >=T1.STARTDATE) 
  OR (T .STARTDATE >= T1.ENDDATE    AND T.ENDDATE <=T1.ENDDATE ) 
  )  AND T.patient =T1.patient AND T.room=T1.room
  AND NOT(T.patient =T1.Patient 
  AND T.Room =T1.Room and T.STARTDATE =T1.STARTDATE 
  AND T.ENDDATE =T1.ENDDATE) ))