找出连续日期的空白 [英] find gaps in sequential dates

问题描述

面临的挑战是找到连续日期中的间隔。

The challenge is to find gaps in sequential dates.

我尝试过类似的操作

WITH t AS (
  SELECT a a, d d, c, ROW_NUMBER() OVER(ORDER BY a) i
  FROM p
)
SELECT MIN(a),MAX(d), c
FROM t
GROUP BY DATEDIFF(day,i,d), c

我正在使用sql数据库。
任何人都可以从这一步中获得帮助吗？
预先感谢

I am using sql database. Anybody can help from this step?? Thanks in advance

推荐答案

您需要标识范围组-但您需要标识分组。这是一种直接在SQL Server 2012+中工作的方法（可以修改以在早期版本中工作）：

You need to identify groups of ranges -- but you need to identify the groupings. Here is a method that works directly in SQL Server 2012+ (and can be modified to work in earlier versions):

• 确定新范围在哪里开始并分配一个范围开始标志。


• 取范围开始的累积和。


• 使用累积和进行聚合。

因此：

select patientid, min(admissiondate), max(dischargedate), sum(cost)
from (select p.*,
             sum(RangeFlag) over (partition by patientid order by admissiondate) as grp
      from (select p.*,
                   (case when admissiondate = dateadd(day, 1,
                                                      lag(dischargedate) over (partition by patientid order by admissiondate))
                         then 0
                         else 1
                    end) as RangeFlag
            from patients p
           ) p
      ) p
group by patientid, grp;

这篇关于找出连续日期的空白的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！