集团通过测序的日期范围只(Group By Date Range of Sequenced Only

我想产生第二行的日期范围的序列，并把日期，如果测序突破。

fldDate        TotalNo
2015-04-01     10
2015-04-02     10
2015-04-03     10
2015-04-04     10
2015-04-05     10
2015-04-06     10
2015-04-07     10
2015-04-08     10
2015-04-09     12
2015-04-10     12
2015-04-11     12
2015-04-12     12
2015-04-20     12
2015-04-21     12
2015-04-22     12
2015-04-23     12
2015-04-24     12
2015-04-25     12

我真的难倒

我想这个表作为

StartDate      EndDate      TotalNo
2015-04-01     2015-04-08   10
2015-04-09     2015-04-12   12
2015-04-20     2015-04-25   12

意味着要么日期范围断裂或TotalNo改变应该创建一个新的行。

我已经做到了，但它不是完全正常工作，我做的一样

SELECT MIN(fldDate) AS StartDate,
       MAX(fldDate) AS EndDate,
       TotalNo
FROM dbo.tbl1
GROUP BY TotalNo
ORDER BY fldDate

它会创建像

StartDate      EndDate      TotalNo
2015-04-01     2015-04-08   10
2015-04-09     2015-04-25   12

Answer 1:

可以通过减去一个整数序列识别组fldDate诸如通过提供- row_number() 。 连续的日期将减法后的值相同。 剩下的只是group by ：

select min(fldDate) as StartDate, max(fldDate) as EndDate, TotalNo
from (select t.*,
             dateadd(day,
                     - row_number() over (partition by TotalNo order by fldDate),
                     fldDate) as grp
      from table t
     ) t
group by TotalNo, grp
order by StartDate, TotalNo;

文章来源: Group By Date Range of Sequenced Only