title: Number based Marker SQL Server 2005
body: <p>I currently have a table function which will be used to remove duplicate employer entries and update the end date.</p>
  
  <p>Currently my table is as follows</p>
  
  <pre><code>Memberid    startdate    enddate    Employerid
  10220      1978-08-07   2007-12-31     1153
  10220      2008-01-01   2011-06-16     1811
  10220      2011-06-17   2011-06-30     1124
  10220      2011-07-01   2011-07-18     1124
  10220      2011-07-19   2011-07-31     1178
  10220      2011-08-01   2011-08-17     1178
  10220      2011-08-18   2011-09-18     1124
  10220      2011-09-19   2011-10-18     1124
  10220      2011-10-19   2011-11-16     1124
  10220      2011-11-17   2011-12-12     1124
  10220      2011-12-13   2012-01-18     1124
  10220      2012-01-19   2012-02-16     1124
  10220      2012-02-17   2012-03-18     1124
  10220      2012-03-19   2012-04-18     1124
  10220      2012-04-19      NULL        1124
  </code></pre>
  
  <p>I would like to put in a counter through a table-valued function so that it counts every time there is an employer change</p>
  
  <p>e.g.</p>
  
  <pre><code>Memberid    startdate    enddate    Employerid    Count
  10220      1978-08-07   2007-12-31     1153        1
  10220      2008-01-01   2011-06-16     1811        2
  10220      2011-06-17   2011-06-30     1124        3
  10220      2011-07-01   2011-07-18     1124        3
  10220      2011-07-19   2011-07-31     1178        4
  10220      2011-08-01   2011-08-17     1178        4
  10220      2011-08-18   2011-09-18     1124        5
  10220      2011-09-19   2011-10-18     1124        5
  10220      2011-10-19   2011-11-16     1124        5
  10220      2011-11-17   2011-12-12     1124        5
  10220      2011-12-13   2012-01-18     1124        5
  10220      2012-01-19   2012-02-16     1124        5
  10220      2012-02-17   2012-03-18     1124        5
  10220      2012-03-19   2012-04-18     1124        5
  10220      2012-04-19      NULL        1124        5
  </code></pre>