使用 SQL 检测连续的日期范围 [英] Detect consecutive dates ranges using SQL

问题描述

我想填充需要开始和结束日期信息的日历对象.我有一个包含日期序列的列.有些日期是连续的(有一天的差异)，有些则不是.

I want to fill the calendar object which requires start and end date information. I have one column which contains a sequence of dates. Some of the dates are consecutive (have one day difference) and some are not.

InfoDate  

2013-12-04  consecutive date [StartDate]
2013-12-05  consecutive date
2013-12-06  consecutive date [EndDate]

2013-12-09                   [startDate]
2013-12-10                   [EndDate]

2014-01-01                   [startDate]
2014-01-02 
2014-01-03                   [EndDate]

2014-01-06                   [startDate]
2014-01-07                   [EndDate]

2014-01-29                   [startDate]
2014-01-30 
2014-01-31                   [EndDate]

2014-02-03                   [startDate]
2014-02-04                   [EndDate]

我想选择每个连续日期范围的开始和结束日期(块中的第一个和最后一个).

I want to pick each consecutive dates range’s start and end date (the first one and the last one in the block).

StartDate     EndDate

2013-12-04    2013-12-06
2013-12-09    2013-12-10
2014-01-01    2014-01-03
2014-01-06    2014-01-07
2014-01-29    2014-01-31
2014-02-03    2014-02-04

我只想用 SQL 解决问题.

I want to solve the problem using SQL only.

推荐答案

不需要连接或递归 CTE.标准的间隙和岛解决方案是按(值减去 row_number)分组，因为它在连续序列中是不变的.开始和结束日期只是组的 MIN() 和 MAX().

No joins or recursive CTEs needed. The standard gaps-and-island solution is to group by (value minus row_number), since that is invariant within a consecutive sequence. The start and end dates are just the MIN() and MAX() of the group.

WITH t AS (
  SELECT InfoDate d,ROW_NUMBER() OVER(ORDER BY InfoDate) i
  FROM @d
  GROUP BY InfoDate
)
SELECT MIN(d),MAX(d)
FROM t
GROUP BY DATEDIFF(day,i,d)

这篇关于使用 SQL 检测连续的日期范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！