title: SQL - Flag consecutive value change in column
body: <p>I'm going to do my best to explain my issue.</p>
  
  <p>I have a dataset that looks similar to the following:</p>
  
  <pre><code>Row#    CusCode Status
  1   100101  Green
  2   100101  Green
  3   100101  Red
  4   100101  Amber
  5   100101  Amber
  6   100101  Green
  7   100101  Red
  8   100101  Red
  9   100101  Red
  10  200909  Red
  11  200909  Amber
  12  200909  Green
  13  200909  Red
  </code></pre>
  
  <p>I am trying to get this view as a next step:</p>
  
  <pre><code>Row#    CusCode Status  Required
  1   100101  Green   1
  2   100101  Green   1
  3   100101  Red 2
  4   100101  Amber   3
  5   100101  Amber   3
  6   100101  Green   4
  7   100101  Red 5
  8   100101  Red 5
  9   100101  Red 5
  10  200909  Red 1
  11  200909  Amber   2
  12  200909  Green   3
  13  200909  Red 4
  </code></pre>
  
  <p>Last step is for the dataset to look as follows:</p>
  
  <pre><code>CusCode Status  Required
  100101  Green   1
  100101  Red 2
  100101  Amber   3
  100101  Green   4
  100101  Red 5
  200909  Red 1
  200909  Amber   2
  200909  Green   3
  200909  Red 4
  </code></pre>
  
  <p><strong>Please note the following:</strong></p>
  
  <ul>
  <li><p>I require that a counter be created based on the status change, irrespective if the status changes to an earlier status, intention is to flag consecutive duplicate records - Table 2 provides an illustration of this.</p></li>
  <li><p>The dataset contains multiple records</p></li>
  <li><p>The order in which the Status is displayed is most important and must output in the same order (Table 3 provides this view).</p></li>
  </ul>
  
  <p>Your assistance will be greatly appreciated. :) </p>