我有一张表格，列出患者一直接受任何形式的药物的情况，这些药物看起来像这样：
Pat ID Type of Admin Order ID Start Date End Date
1 Medication 1 01/01/2016 04:20 01/04/2016 05:30
1 Medication 2 01/03/2016 18:30 01/07/2016 10:15
我想知道确切的重叠日期范围。因此，结果表如下所示：
Pat ID First_Overlap Last_Overlap
1 01/03/2016 18:30 01/04/2016 05:30
我正在使用Netezza。有谁知道如何做到这一点？
我对Netezza并不熟悉，但是如果我要在MySQL或其他SQL变体中执行此操作，则会执行以下操作：
SELECT C.PatID AS PatID, MAX(C.FirstOverlap) AS FirstOverlap, MIN(C.LastOverlap) As LastOverlap
FROM (
SELECT A.PatID AS PatID, CASE WHEN B.StartDate <= A.EndDate THEN B.StartDate ELSE A.EndDate END AS FirstOverlap,
CASE WHEN B.EndDate >= A.StartDate THEN B.EndDate ELSE A.StartDate END AS LastOverlap
FROM careAdmin AS A LEFT JOIN careAdmin AS B ON A.PatID = B.PatID AND A.OrderID = B.OrderID
WHERE NOT B.EndDate <= A.StartDate OR B.StartDate >= A.EndDate
) C
GROUP BY C.PatID
即使语法可能稍有不同，也有望帮助您找到正确的答案。
问候
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句