title: SQL FIFO Logic in sql server 2008
body: <p>I am sorry before if that title doesn't represent my problem here.</p>
  
  <p>I have table 1 with multiple items.</p>
  
  <p>TABLE 1                 </p>
  
  <pre><code>|No|Item|Quantity X|    
  |1 |A   |20        |
  |2 |A   |21        |
  |3 |A   |10        |
  |4 |B   |3         |
  |5 |B   |4         |
  ...
  </code></pre>
  
  <p>My custom FIFO function result for item A:</p>
  
  <pre><code>|No|Name     |Quantity Y|           
  |1 |Document1|2         |   
  |2 |Document2|20        |   
  |3 |Document3|5         |   
  |4 |Document4|1         |   
  |5 |Document5|22        |           
  </code></pre>
  
  <p>My custom FIFO function result for item B:  </p>
  
  <pre><code>|No|Name       |Quantity Y|         
  |1 |Document100|2         | 
  |2 |Document101|2         | 
  |3 |Document102|4         | 
  </code></pre>
  
  <p>The expected result: </p>
  
  <p>TABLE 2</p>
  
  <pre><code>Item|Quantity X|Name       |Quantity Y|
  A   |20        |Document1  |2         |
  A   |20        |Document2  |18        |
  A   |21        |Document2  |2         |
  A   |21        |Document3  |5         |
  A   |21        |Document4  |1         |
  A   |21        |Document5  |13        |
  A   |10        |Document5  |10        |
  B   |3         |Document101|2         |
  B   |3         |Document102|1         |
  B   |4         |Document102|1         |
  B   |4         |Document103|3         |
  </code></pre>
  
  <p>I have using SQL Server 2008. How to achieve this with SQL?</p>