title: Incremental count in SQL Server 2005
body: <p>I am working with a Raiser's Edge database using SQL Server 2005. I have written SQL that will produce a temporary table containing details of direct debit instalments. Below is a small table containing the key variables for the question I'm going to ask, with some fictional data:    </p>
  
  <pre><code>Donor_ID    Instalment_ID    Instalment_Date    Amount 
  1234        1111             01/01/2011         £5.00
  1234        1112             01/02/2011         £0.00
  1234        1113             01/03/2011         £5.00
  1234        1114             01/04/2011         £5.00
  1234        1115             01/05/2011         £0.00
  1234        1116             01/06/2011         £0.00
  2345        2111             01/01/2011         £0.00
  2345        2112             01/02/2011         £5.00
  2345        2113             01/03/2011         £5.00
  2345        2114             01/04/2011         £0.00
  2345        2115             01/05/2011         £0.00
  2345        2116             01/06/2011         £0.00
  </code></pre>
  
  <p>As you will see, some of the values in the Amount column are £0.00. This can occur when a donor has insufficient funds in their account, for example.</p>
  
  <p>What I'd like to do is write a SQL query that will create a field containing an incremental count of consecutive £0.00 payments that resets after a non-£0.00 payment or after a change in Donor_ID. I have reproduced the above data below, with the field I'd like to see.</p>
  
  <pre><code>Donor_ID    Instalment_ID    Instalment_Date    Amount    New_Field
  1234        1111             01/01/2011         £5.00     
  1234        1112             01/02/2011         £0.00     1
  1234        1113             01/03/2011         £5.00
  1234        1114             01/04/2011         £5.00
  1234        1115             01/05/2011         £0.00     1
  1234        1116             01/06/2011         £0.00     2
  2345        2111             01/01/2011         £0.00     1
  2345        2112             01/02/2011         £5.00
  2345        2113             01/03/2011         £5.00
  2345        2114             01/04/2011         £0.00     1
  2345        2115             01/05/2011         £0.00     2
  2345        2116             01/06/2011         £0.00     3
  </code></pre>
  
  <p>To help clarify what I'm looking for, I think what I'm looking to do would be similar to a winning streak field on a list of a football team's results. For example:</p>
  
  <pre><code>Opponent     Score    Winning_Streak
  Arsenal      1-0      1
  Liverpool    0-0
  Swansea      3-1      1
  Chelsea      2-1      2
  Fulham       4-0      3
  Stoke        0-0      
  Man Utd      1-3
  Reading      2-1      1
  </code></pre>
  
  <p>I've considered various options, but have made no progress. Unless I've missed something obvious, I think that a solution more advanced than my current SQL programming level might be required. </p>