确定sql间隙和孤岛中的连续日期

daolsyd0 于 2021-07-26 发布在 Java

关注(0)|答案(1)|浏览(377)

我的情况是，一个病人可以接受多种服务。这些服务可以有重叠的日期，也可以有间隔和孤岛。我正在尝试编写一个查询，它将显示患者接受某种服务的连续时间长度。
下表如下：

CREATE TABLE #tt
(Patient    VARCHAR(10), StartDate DATETIME, EndDate DATETIME)
INSERT INTO #tt
VALUES
('Smith',   '2014-04-13',   '2014-06-04'),
('Smith',   '2014-05-07',   '2014-05-08'),
('Smith',   '2014-06-21',   '2014-09-19'),
('Smith',   '2014-08-27',   '2014-08-27'),
('Smith',   '2014-08-28',   '2014-09-19'),
('Smith',   '2014-10-30',   '2014-12-16'),
('Smith',   '2015-05-21',   '2015-07-03'),
('Smith',   '2015-05-22',   '2015-07-03'),
('Smith',   '2015-05-26',   '2015-11-30'),
('Smith',   '2015-06-25',   '2016-06-08'),
('Smith',   '2015-07-22',   '2015-10-22'),
('Smith',   '2016-08-11',   '2016-09-02'),
('Smith',   '2017-06-02',   '2050-01-01'),
('Smith',   '2017-12-22',   '2017-12-22'),
('Smith',   '2018-03-25',   '2018-06-30')

如你所见，许多日期重叠。最终我想看到的是以下结果，它将显示患者接受至少一项服务的日期，如下所示：

Patient     |StartDate        |EndDate
--------------------------------------
Smith       |2014-04-13       |2016-06-04
Smith       |2014-06-21       |2014-09-19
Smith       |2014-10-30       |2014-12-16
Smith       |2015-05-21       |2016-06-08
Smith       |2016-08-11       |2016-09-02
Smith       |2017-06-02       |2050-01-01

我对sql代码中的各种漏洞和孤岛感到眼花缭乱。我已经开始使用这个cte，但显然它不起作用，如果我想要这个，我可以简单地使用select phn，min（startdate），max（enddate）

WITH HCC_PAT 
AS 
(
    SELECT DISTINCT
    PHN,
    StartDate,
    EndDate,
    MIN (StartDate) OVER (  PARTITION BY  PHN ORDER BY StartDate
                                        ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS PreviousStartDate,
    MAX (EndDate) OVER (    PARTITION BY  PHN ORDER BY EndDate
                                        ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS PreviousEndDate 

FROM    #tt)

SELECT  DISTINCT --hcc_Pat.HCCClientKey,
        hcc_pat.PHN,
        hcc_pat.StartDate,
        ISNULL (LEAD (PreviousEndDate) OVER (PARTITION BY PHN ORDER BY ENDDATE), 'January 1, 2050') AS EndDate
FROM    HCC_PAT
WHERE   PreviousEndDate > StartDate 
AND     (StartDate < PreviousStartDate OR PreviousStartDate IS NULL)

在这一点上任何帮助都将不胜感激

sql common-table-expression date-range gaps-and-islands

来源：https://stackoverflow.com/questions/62880757/sql-combine-dates

1条答案

按热度按时间

oewdyzsn1#

一种方法将日期分散开来，并用一个指示器指示服务是开始还是结束。然后，可以使用指标的累计和来定义不同的组——累计和中的零值是一个时段结束时的值。
最后一步是聚合：

with d as (
      select patient, startdate as dte, 1 as inc from tt
      union all
      select patient, enddate as dte, -1 as inc from tt
     ),
     dd as (
       select patient, dte, sum(sum(inc)) over (order by dte) as cume_inc
       from d
       group by patient, dte
      ),
     ddd as (
       select dd.*, sum(case when cume_inc = 0 then 1 else 0 end) over (partition by patient order by dte desc) as grp
       from dd
      )
select patient, min(dte) as startdate, max(dte) as enddate
from ddd
group by grp;

这是一个sql小提琴。

赞(0）回复(0）举报 2021-07-26

我来回答

确定sql间隙和孤岛中的连续日期

1条答案

相关问题

热门标签

最新问答