title: Count by date range between min date and max date
body: <p>I'm trying to return a count by date range between min date and max date for projects.
  My table contains:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>RecordID</th>
  <th>ProjectID</th>
  <th>StartDate</th>
  <th>EndDate</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1254879</td>
  <td>00025</td>
  <td>2021-01-01</td>
  <td>2021-02-28</td>
  </tr>
  <tr>
  <td>1254248</td>
  <td>00025</td>
  <td>2021-03-01</td>
  <td>2021-03-31</td>
  </tr>
  <tr>
  <td>1253598</td>
  <td>00025</td>
  <td>2021-03-01</td>
  <td>2021-03-31</td>
  </tr>
  <tr>
  <td>1253578</td>
  <td>00025</td>
  <td>2021-04-01</td>
  <td>2021-04-30</td>
  </tr>
  <tr>
  <td>1253698</td>
  <td>00025</td>
  <td>2021-04-01</td>
  <td>2021-04-30</td>
  </tr>
  <tr>
  <td>1254873</td>
  <td>00025</td>
  <td>2021-04-01</td>
  <td>2021-04-30</td>
  </tr>
  <tr>
  <td>1253697</td>
  <td>00259</td>
  <td>2021-02-01</td>
  <td>2021-02-28</td>
  </tr>
  <tr>
  <td>1253570</td>
  <td>00259</td>
  <td>2021-02-01</td>
  <td>2021-02-28</td>
  </tr>
  <tr>
  <td>1254999</td>
  <td>00037</td>
  <td>2021-01-01</td>
  <td>2021-01-31</td>
  </tr>
  <tr>
  <td>1252695</td>
  <td>00037</td>
  <td>2021-02-01</td>
  <td>2021-02-28</td>
  </tr>
  <tr>
  <td>1254889</td>
  <td>00037</td>
  <td>2021-05-01</td>
  <td>2021-06-30</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>The expected result is:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>ProjectID</th>
  <th>Sequence</th>
  <th>StartDate</th>
  <th>EndDate</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>00025</td>
  <td>01</td>
  <td>2021-01-01</td>
  <td>2021-02-28</td>
  </tr>
  <tr>
  <td>00025</td>
  <td>02</td>
  <td>2021-03-01</td>
  <td>2021-03-31</td>
  </tr>
  <tr>
  <td>00025</td>
  <td>02</td>
  <td>2021-03-01</td>
  <td>2021-03-31</td>
  </tr>
  <tr>
  <td>00025</td>
  <td>03</td>
  <td>2021-04-01</td>
  <td>2021-04-30</td>
  </tr>
  <tr>
  <td>00025</td>
  <td>03</td>
  <td>2021-04-01</td>
  <td>2021-04-30</td>
  </tr>
  <tr>
  <td>00025</td>
  <td>03</td>
  <td>2021-04-01</td>
  <td>2021-04-30</td>
  </tr>
  <tr>
  <td>00259</td>
  <td>01</td>
  <td>2021-02-01</td>
  <td>2021-02-28</td>
  </tr>
  <tr>
  <td>00259</td>
  <td>01</td>
  <td>2021-02-01</td>
  <td>2021-02-28</td>
  </tr>
  <tr>
  <td>00037</td>
  <td>01</td>
  <td>2021-01-01</td>
  <td>2021-01-31</td>
  </tr>
  <tr>
  <td>00037</td>
  <td>02</td>
  <td>2021-02-01</td>
  <td>2021-02-28</td>
  </tr>
  <tr>
  <td>00037</td>
  <td>03</td>
  <td>2021-05-01</td>
  <td>2021-06-30</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>I tried some queries and the most complex query that I've used without success was:</p>
  <pre><code>select 
      ProjectID
      ,count(1) as Sequence
      ,min(StartDate) as StartDate
      ,max(EndDate) as EndDate
  from(
      select 
          t.*
          ,row_number() over(order by EndDate) as sequence
          ,row_number() over(partition by RecordID order by EndDate) as sequence_t
      from 
          dbo.ProjectTable as t
  ) as t
  group by
      ProjectID
      ,(sequence - sequence_t)
  order by
      min(EndDate)
  </code></pre>
  <p>I don't know how to return the expected value in sequence once count in this query and other queries that I tried return only 1 for each record.</p>
  <p>Below the DDL</p>
  <p>Create Table:</p>
  <pre><code>create table [dbo].[ProjectTable](
      [RecordID] int NOT NULL,
      [ProjectID] int NOT NULL,
      [StartDate] date NULL,
      [EndDate] date NULL 
  ) on [primary]
  go
  </code></pre>
  <p>Insert Data:</p>
  <pre><code>insert into [dbo].[ProjectTable] values
      (1254879,00025,'2021-01-01','2021-02-28'),
      (1254248,00025,'2021-03-01','2021-03-31'),
      (1253598,00025,'2021-03-01','2021-03-31'),
      (1253578,00025,'2021-04-01','2021-04-30'),
      (1253698,00025,'2021-04-01','2021-04-30'),
      (1254873,00025,'2021-04-01','2021-04-30'),
      (1253697,00259,'2021-02-01','2021-02-28'),
      (1253570,00259,'2021-02-01','2021-02-28'),
      (1254999,00037,'2021-01-01','2021-01-31'),
      (1252695,00037,'2021-02-01','2021-02-28'),
      (1254889,00037,'2021-05-01','2021-06-30')
  go
  </code></pre>