title: Query and Partition By clause group by window
body: <p>I've the following code</p>
  
  <pre><code>declare @test table (id int, [Status] int, [Date] date)
  
  insert into @test (Id,[Status],[Date]) VALUES
      (1,1,'2018-01-01'),
      (2,1,'2018-01-01'),
      (1,1,'2017-11-01'),
      (1,2,'2017-10-01'),
      (1,1,'2017-09-01'),
      (2,2,'2017-01-01'),
      (1,1,'2017-08-01'),
      (1,1,'2017-07-01'),
      (1,1,'2017-06-01'),
      (1,2,'2017-05-01'),
      (1,1,'2017-04-01'),
      (1,1,'2017-03-01'),
      (1,1,'2017-01-01')
  
  SELECT
      id,
      [Status],
  MIN([Date]) OVER (PARTITION BY id,[Status] ORDER BY [Date],id,[Status] ) as WindowStart,
  max([Date]) OVER (PARTITION BY id,[Status] ORDER BY [Date],id,[Status]) as WindowEnd,
  COUNT(*) OVER (PARTITION BY id,[Status] ORDER BY [Date],id,[Status] ) as total
  from @test
  </code></pre>
  
  <p>But the result is this:</p>
  
  <pre><code>id  Status  WindowStart WindowEnd   total
  1   1   2017-01-01  2017-01-01  1
  1   1   2017-01-01  2017-03-01  2
  1   1   2017-01-01  2017-04-01  3
  1   1   2017-01-01  2017-06-01  4
  1   1   2017-01-01  2017-07-01  5
  1   1   2017-01-01  2017-08-01  6
  1   1   2017-01-01  2017-09-01  7
  1   1   2017-01-01  2017-11-01  8
  1   1   2017-01-01  2018-01-01  9
  1   2   2017-05-01  2017-05-01  1
  1   2   2017-05-01  2017-10-01  2
  2   1   2018-01-01  2018-01-01  1
  2   2   2017-01-01  2017-01-01  1
  </code></pre>
  
  <p>And I need to be grouped by window like this.</p>
  
  <pre><code>id  Status  WindowStart WindowEnd   total
  1   1   2017-01-01  2017-04-01  3
  1   2   2017-05-01  2017-05-01  1
  1   1   2017-06-01  2017-09-01  4
  1   2   2017-10-01  2017-10-01  1
  1   1   2017-11-01  2018-01-01  2
  2   1   2018-01-01  2018-01-01  1
  2   2   2017-01-01  2017-01-01  1
  </code></pre>
  
  <p>The first group for the id= 1 Status = 1 should end at the first row with Status = 2 (2017-05-01) so the total is 3 and then start again from the 2017-06-01 to 2017-09-01 with a total of 4 rows.</p>
  
  <p>How can get this done?</p>