Group By Date Range of Sequenced Only

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

  • SQL Server : how to select a fixed amount of rows (select every x-th value)
  • Efficient Ad-hoc SQL OLAP Structure
  • T-SQL USE satetement in condition
  • SQL Server stored procedure to insert in multiple tables
  • Perl Not Reading Full Values from Database
  • Can CONTAINSTABLE be told which column to look in per search term?
  • 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
    

    One Solution collect form web for “Group By Date Range of Sequenced Only”

    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;
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.