SQL:按组的最大可能日期范围 [英] SQL: Maximum possible date range by group
本文介绍了SQL:按组的最大可能日期范围的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在使用T-SQL(Microsoft SQL Server Management Studio 2017),并且具有类似于以下内容的数据集:
I am using T-SQL (Microsoft SQL Server Management Studio 2017) and I have a data set that resembles the following:
BEGINDATE ENDDATE ID
2015-07-01 2015-07-12 1
2015-07-01 2015-07-12 1
2015-07-11 2015-07-15 1
2015-07-18 2015-08-04 1
2015-06-28 2015-07-04 2
2015-06-28 2015-07-03 2
2015-06-29 2015-07-04 2
2015-07-03 2015-07-15 2
2015-07-17 2015-07-20 2
我想做的是按ID合并重叠的日期(有一些例子可以做到,但不能按组-像这样)。
What I would like to do is to merge overlapping dates by ID (There are a few examples out there that do this but not by group - like this one).
理想情况下,最终结果将类似于:
Ideally, the end result would be something like:
BEGINDATE ENDDATE ID
2015-07-01 2015-07-15 1
2015-07-18 2015-08-04 1
2015-06-28 2015-07-15 2
2015-07-17 2015-07-20 2
A ny的建议?
推荐答案
使用即席日历表查找差距与岛屿的解决方案:
Using an adhoc calendar table for a gaps-and-islands solution:
declare @fromdate date, @thrudate date;
select @fromdate = min(begindate), @thrudate = max(enddate) from t;
;with n as (select n from (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) t(n))
, dates as (
select top (datediff(day, @fromdate, @thrudate)+1)
[Date]=convert(date,dateadd(day,row_number() over(order by (select 1))-1,@fromdate))
from n as deka cross join n as hecto cross join n as kilo
cross join n as tenK cross join n as hundredK
order by [Date]
)
, cte as (
select
t.id
, d.date
, grp = row_number() over (partition by t.id order by d.date)
- datediff(day,@fromdate,d.date)
from dates d
inner join t
on d.date >= t.begindate
and d.date <= t.enddate
group by t.id, d.date
)
select
BeginDate = min(cte.date)
, EndDate = max(cte.date)
, id
from cte
where id is not null
group by id, grp
order by id, BeginDate
上个演示: http://rextester.com/NKGY7104
返回值:
+------------+------------+----+
| BeginDate | EndDate | id |
+------------+------------+----+
| 2015-07-01 | 2015-07-15 | 1 |
| 2015-07-18 | 2015-08-04 | 1 |
| 2015-06-28 | 2015-07-15 | 2 |
| 2015-07-17 | 2015-07-20 | 2 |
+------------+------------+----+
这篇关于SQL:按组的最大可能日期范围的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文