本文介绍了找出连续日期的空白的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
面临的挑战是找到连续日期中的间隔。
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;更多推荐
找出连续日期的空白
发布评论