title: SELF JOIN to Update Rows Of the Table
body: <p>I have a Table with these columns</p>
  
  <pre><code>- ProjectId
   - Generation
   - Expected 
   - CarryOver
  </code></pre>
  
  <p>I am trying to update my already populated table in this fashion:</p>
  
  <pre><code>  Generation = Integral Part of ((Generation + CarryOver of Previous Row)/10 )
    CarryOver =  decimal part of  ((Generation + CarryOver of Previous Row)/10 )
  </code></pre>
  
  <p>where Previous Row and Current Row both have same projectId</p>
  
  <p>Below is the query am using to achieve this:</p>
  
  <pre><code>UPDATE TTable 
  
  SET 
  
  TTable.Expected=(TTable.Generation+ ISNULL(STable.CarryOver,0)),
  TTable.CarryOver =(TTable.Generation+ISNULL(STable.CarryOver,0))-CONVERT(INT,(TTable.Generation+ISNULL(STable.CarryOver,0)))
  
  FROM
  (
      SELECT ROW_NUMBER()OVER(order by ProjectId,MonthYear) as RowNumber,ProjectId, 
      [MonthYear],[Month],[Generation],[Expected],[CarryOver]
      FROM #SRECEsimated 
  )TTable,
  
  (
      SELECT ROW_NUMBER()OVER(order by ProjectId,MonthYear) as RowNumber,ProjectId,
          [MonthYear],[Month],[Generation],[Expected],[CarryOver] 
      FROM #SRECEsimated 
  ) STable
  
  
  Where   
  TTable.RowNumber  = STable.RowNumber+1 AND
  TTable.ProjectId = STable.ProjectId 
  </code></pre>
  
  <p>....but something strange happens, update happens only for first two rows. For other rows, 
  <code>ISNULL(STable.CarryOver,0)</code> returns 0. why??
  Please help me. or suggest some other way to achieve this</p>
  
  <p>EDIT: on running the query </p>
  
  <pre><code>ProjectId   MonthYear   Month   Year    Generation  Expected    CarryOver
  10  2011-10-01 00:00:00.000 10  2011    56.748             56            0.748
  10  2011-11-01 00:00:00.000 11  2011    12.004             12            0.752
  10  2011-12-01 00:00:00.000 12  2011    10.632             10            0.632
  10  2012-01-01 00:00:00.000 01  2012    11.928             11            0.928
  10  2012-02-01 00:00:00.000 02  2012    7.580               7            0.580
  100 2011-12-01 00:00:00.000 12  2011    5.897               5            0.897
  100 2012-01-01 00:00:00.000 01  2012    0.881               1            0.778
  </code></pre>
  
  <p>data is generated as shown above. notice how the logic doesn't work after  3row</p>
  
  <p>Original Ouput. Before running the update query:</p>
  
  <pre><code>ProjectId   MonthYear   Month   Year    Generation  Expected    CarryOver
  10  2011-10-01 00:00:00.000 10  2011    56.748            56             0.748
  10  2011-11-01 00:00:00.000 11  2011    12.004            NULL           NULL
  10  2011-12-01 00:00:00.000 12  2011    10.632            NULL           NULL
  10  2012-01-01 00:00:00.000 01  2012    11.928            NULL           NULL
  10  2012-02-01 00:00:00.000 02  2012    7.580             NULL           NULL
  100 2011-12-01 00:00:00.000 12  2011    5.897             5           0.897
  100 2012-01-01 00:00:00.000 01  2012    0.881             NULL           NULL
  </code></pre>