title: How to find two consecutive rows sorted by date, containing a specific value?
body: <p>I have a table with the following structure and data in it:</p>
  
  <pre><code>| ID    | Date          | Result    |
  |----   |------------   |--------   |
  | 1     | 30/04/2020    | +         |
  | 1     | 01/05/2020    | -         |
  | 1     | 05/05/2020    | -         |
  | 2     | 03/05/2020    | -         |
  | 2     | 04/05/2020    | +         |
  | 2     | 05/05/2020    | -         |
  | 2     | 06/05/2020    | -         |
  | 3     | 01/05/2020    | -         |
  | 3     | 02/05/2020    | -         |
  | 3     | 03/05/2020    | -         |
  | 3     | 04/05/2020    | -         |
  </code></pre>
  
  <p>I'm trying to write an SQL query (I'm using SQL Server) which returns the date of the first two <strong>consecutive</strong> negative results for a given ID. 
  For example, for ID no. 1, the first two consecutive negative results are on 01/05 and 05/05. 
  The first two consecutive results for ID No. 2 are on 05/05 and 06/05. 
  The first two consecutive negative results for ID No. 3 are on on 01/05 and 02/05 . </p>
  
  <p>So the query should produce the following result: </p>
  
  <pre><code>| ID    | FirstNegativeDate     |
  |----   |-------------------    |
  | 1     | 01/05                 |
  | 2     | 05/05                 |
  | 3     | 01/05                 |
  </code></pre>
  
  <p>Please note that the dates aren't necessarily one day apart. Sometimes, two consecutive negative tests may be several days apart. But they should still be considered as "consecutive negative tests". In other words, two negative tests are not 'consecutive' only if there is a positive test result in between them. </p>
  
  <p>How can this be done in SQL? I've done some reading and it looks like maybe the PARTITION BY statement is required but I'm not sure how it works. </p>