仅按顺序分组按日期范围 [英] Group By Date Range of Sequenced Only

问题描述

  fldDate TotalNo $ 
 
我试着生成日期范围序列，并将日期放在第二行。 b $ b 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更改它应该创建一个新行。 p>

我已经完成了，但没有完成













$ 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 
  

解决您可以通过从 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 
按StartDate排序，TotalNo; 
  

I am trying to generate a date range sequence and put date in second row if sequencing is break.

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

I am really stumped

I want this table as

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

Means Either date range breaks or TotalNo change It should create a new row.

I have done it but its not working completely I am doing like

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

It will create like

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

解决方案

You can identify the groups by subtracting an integer sequence from the fldDate -- such as provided by row_number(). Consecutive dates will have the same value after the subtraction. The rest is just 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;

这篇关于仅按顺序分组按日期范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！