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

  • Calling a UDF in LINQ without “dbo” database user
  • Query Involving Linked Server Raises Distributed Transaction Error When No Transaction is Used or Needed
  • SSIS. Load the same data from web-service to different tables
  • Exception Handling not handling Error in SQL-SERVER
  • Nested Repeaters and SqlDataSource Parameters
  • How to drop and recreate indexes without growing the transaction log
  • 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.