找出连续日期的空白

本文介绍了找出连续日期的空白的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

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

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;